Excel help please??
 

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.

use MAX(CELL1:CELLn)

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

Seemed to work for me.

Good Luck.

Ahh and I see La Mer just beat me to it ....

Thanks for that!!

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

Twodogs

I think I worked it out!!!

=MAX(A534,C534,E534)

Twodogs

Thanks again for your help!!!

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

A simple If soles the problem :


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

Good Luck,

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


should do it

Thanks Guys

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

Cheers
Twodogs

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 :)

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

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

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

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

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")))

=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

Don't worry I just tried it again now it works. Beats me I didn't change anything??

Thanks guys

Twodogs

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

Thanks La Mer

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

Beauty
Twodogs

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

=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?

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.

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