Formula Advice

[beef]

Hi

I seek advice on a formula that I am trying to use in a spreadsheet.

There are 3 formulas in cells E1, E2 and E3 all work OK

I have put all the formulas into one, which is what I would like to have.

This is contained in cell B12. You will note that I have not placed the = symbol at this point as I get an error message.

I am not sure I the formula is to long or what is wrong.

Another pair of eyes may find where I have gone wrong.

Attached is the excel spreadsheet.

 

[Alansidman]

=IF(C1=1,"medium",IF(OR(C2=1,C6=1,C10=1),"Low",IF(OR(C4=1,C5=1,C7=1,C8=1,C9=1),"High","")))

I have changed your formula slightly, but it will only give you correct results if only one of the if statements is true. If they are all true, it will return the result for the first true statement it encounters as it will ignore the balance of the if statement. To ensure that you get correct results, I think that you need to keep it in three statements unless you are looking for the first true event.

I believe that the formula means the same thing as yours except for the order which can be changed.

Alan

 

[Brianwarnock]

There are too many nested Ifs, use the Or operator, mindyou not sure what you are doing.

=IF(OR(C3=1,C4=1,C5=1,C6=1,C7=1,C8=1,C9=1),"HIGH",IF(C1=1,"MEDIUM",IF(OR(C6=1,C10=1),"LOW","")))


Brian

Edit Oh I see I was too slow.

 

[beef]

Hi Brian

Many thanks for that formula, not something I would have come up with

 

[beef]

Hi Alan

many thanks for your contribution as well much appreciated.

 

[TonySea]

Note that Excel always used to be limited to 6 nested IF statements

 

[beef]

Hi Tony

Many thanks for that advice