PDA

View Full Version : Excel help please??


Twodogs
5th March 2006, 01:24 PM
Could anyone point me in the right direction?

I need a formula for the following! I have 3 columns with the 3 different state divs in them. In the fourth column I want to put the highest div of the 3 columns. Of course in some cases the three divs could be all the same. Anybody have any suggestions it would be greatly appreciated.

Thanks
Twodogs

La Mer
5th March 2006, 01:44 PM
Could anyone point me in the right direction?

I need a formula for the following! I have 3 columns with the 3 different state divs in them. In the fourth column I want to put the highest div of the 3 columns. Of course in some cases the three divs could be all the same. Anybody have any suggestions it would be greatly appreciated. Thanks Twodogs

Assuming you have columns A B C D, with cell A1 containing 4.30 B1 containing 4.6 & C1 containing 4.20 then in column D1 place the following formula:
=MAX(A1:C1)

That should do it with the answer being 4.6 being shown in cell D1.

wesmip1
5th March 2006, 01:45 PM
use MAX(CELL1:CELLn)

where CELL1 is the first column an CELLn is the last column.

Seemed to work for me.

Good Luck.

wesmip1
5th March 2006, 01:46 PM
Ahh and I see La Mer just beat me to it ....

Twodogs
5th March 2006, 01:51 PM
Thanks for that!!

What if the columns were not side by side?? Say column A, C and E

Twodogs

Twodogs
5th March 2006, 01:56 PM
I think I worked it out!!!

=MAX(A534,C534,E534)

Twodogs

Thanks again for your help!!!

Twodogs
7th March 2006, 12:36 PM
Any ideas on how I can overcome getting an answer #DIV/0! I am using the following formula but the problem is that some of the data is zeros and I get the answer #DIV/0! when it it (0/0)*100.

=('Full Fields'!BZ7/'Full Fields'!BY7)*100

Is there an alternative formula that I could use to overcome this?

Thanks
Twodogs

wesmip1
7th March 2006, 12:47 PM
A simple If soles the problem :


=IF('Full Fields'!BY7 = 0, 0, ('Full Fields'!BZ7/'Full Fields'!BY7)*100)

Good Luck,

davez
7th March 2006, 12:49 PM
=IF(ISERROR('Full Fields'!BZ7/'Full Fields'!BY7)*100,0,'Full Fields'!BZ7/'Full Fields'!BY7*100)


should do it

Twodogs
7th March 2006, 12:55 PM
Thanks Guys

You certainly know your excel. I am learning but slowly.

Cheers
Twodogs

Punter76
7th March 2006, 09:48 PM
Can anyone please help with this excel question too?

I have a whole heap of fomulas, example... A = B + C...

.. now if B + C has no text then I have a '0' everywhere, or many of them all over the page.

How do I stop these from appearing so that a number appears only when numbers are entered into the columns I am adding up?

Phew.. that was confusing :)

Shaun
7th March 2006, 10:12 PM
Look at the fist cell that is referenced example in cel D1 we have a formula

=(A1+C1)/100
change it to this

=(If A1="","",(A1+C1)/100

to explain what it is doing

If A1 = nothing , then nothing , otherwise perform the formula

Punter76
7th March 2006, 10:24 PM
Or.. options.. tick 'Hide zero values'.

I just found that one!

davez
7th March 2006, 11:44 PM
Or.. options.. tick 'Hide zero values'.

I just found that one!

yeh, but not real useful p76 when zeros are a required value within your spreadsheet, not recommended in other words

Twodogs
26th April 2006, 07:28 AM
Good Morning Excel Champs,

Is it possible to have more than one IF?

What I want to do is look at 3 columns and IF they meet requirements I then perform a formula on a fourth column.

Is this where AND comes into the formula setup?

Thanks
Twodogs

xptdriver
26th April 2006, 08:10 AM
Good Morning Excel Champs,

Is it possible to have more than one IF?

(snip)

Thanks
Twodogs

I am thinking I know the answer to this one... I think you can have up to 7 IF's.. tho the gurus will know for sure

La Mer
26th April 2006, 08:11 AM
Good Morning Excel Champs,

Is it possible to have more than one IF?

What I want to do is look at 3 columns and IF they meet requirements I then perform a formula on a fourth column.

Is this where AND comes into the formula setup?

Thanks
Twodogs

You can have as many as seven IF's in any single formula. The AND can be used in conjunction with IF to test if two variables are met.

Here is one that I use in one of my programs:
=IF(AND(85<=Q4,Q5<=75),"A",IF(Q6<=75,"B",IF(AND(75>=Q6,Q7<=65),"C","D")))

Twodogs
26th April 2006, 08:13 AM
=IF(AND(O7<2.5,Q7<35.2,U7>0),1,0)

I just tried the above formula but although the three columns meet criteria I still get 0 rather than a 1

Have I balls up somewhere?

Twodogs

Twodogs
26th April 2006, 08:16 AM
Don't worry I just tried it again now it works. Beats me I didn't change anything??

Thanks guys

Twodogs

La Mer
26th April 2006, 08:17 AM
=IF(AND(O7<2.5,Q7<35.2,U7>0),1,0)

I just tried the above formula but although the three columns meet criteria I still get 0 rather than a 1 Have I balls up somewhere? Twodogs

See my previous response, there is a formula shown there that I use in one of my programs.

Twodogs
26th April 2006, 08:19 AM
Thanks La Mer

Just looking at it now. Might be able to use that as well.

Beauty
Twodogs

Twodogs
26th April 2006, 08:25 AM
Any hints for if I want within that formula to say Q7>22 and less than 35.

=IF(AND(O7<2.5,Q7>22OR<35.2,U7>0),1,0) wouldn't work would it??

Twodogs

Twodogs
26th April 2006, 09:22 AM
=IF(AND(O7<2.5,((Q7>22)*OR(Q7<35.2,))*U7>0),1,0)
This seems to work but it was a solution offered by excel itself. I don't understand how it works. Can someone explain please?

Thanks
Twodogs

La Mer
26th April 2006, 01:30 PM
=IF(AND(O7<2.5,((Q7>22)*OR(Q7<35.2,))*U7>0),1,0)
This seems to work but it was a solution offered by excel itself. I don't understand how it works. Can someone explain please? Thanks Twodogs

What exactly are you attempting to achieve Twodogs?

Twodogs
26th April 2006, 01:36 PM
Howdy La Mer

I have 3 columns with data in and a fourth I want to put either 1 or 0 in if the other 3 columns have all got the the correct data I am after.

I am looking for the following in the 3 columns

column 1 must be less than 2.5
column 2 must be between 22 and 35.3
column 3 must be greater than 0

If all agree I get a 1 in column 4 if not 0

Twodogs

La Mer
26th April 2006, 02:47 PM
Howdy La Mer

I have 3 columns with data in and a fourth I want to put either 1 or 0 in if the other 3 columns have all got the the correct data I am after.

I am looking for the following in the 3 columns

column 1 must be less than 2.5
column 2 must be between 22 and 35.3
column 3 must be greater than 0

If all agree I get a 1 in column 4 if not 0

Twodogs

Try this: =IF(A1<2.5,IF(B1>=22,IF(B1<=35.3,IF(C1>0,1,0)),0),0)

I've done a quick test & it appears to work OK. No need for the AND statement. Best of luck.

Twodogs
26th April 2006, 02:53 PM
Thanks La Mer

The other formula I posted works but I would rather understand how it works.Will have a play thanks for your help today La Mer

Twodogs