[Solved] Nested IF or AND function

[saidtomuch]

Help me please. no sleep at the weekend.

I am building a spreadsheet where I want the result to be a "then" but by carrying out 2 tests.

ie (d1>a2 and a3 > b2;"U-H")

if I try =if(d1>a2; a3 > b2;"U-H") result err***
if I try =and(d1>a2; a3 > b2;"U-H") result = value

I want the result to be U-H in this case.

[keme]

=IF(AND(d1>a2; a3 > b2);"U-H")

You may want to add the third parameter (the "else" part). Otherwise You'll get "FALSE" displayed in the cell.

=IF(AND(d1>a2; a3 > b2);"U-H";"Something else")

[saidtomuch]

Many many thanks Keme, can I be rude and ask if additional tests can be added to deliver different results.

ie. =IF(AND(d1>a2; a3 > b2);"U-H"); AND(D1>A2; A3> C2);"U-HC"

and so on where the 1st test remains the same d1>a2 but a number of different 2nd tests deliver different results.

Many thanks again.

[Hagar Delest]

You can associate several levels of conditions. Just keep the structure of the command:
IF(Condition;Result;Else)
In Condition, put your tests (like AND(...;...;...))
Result is what you get if the tests are all true
Else is what you get if at least one test is false.

Check the OOo help file or the formula wizard to get further information (there is also a link to the online guide at the top of the Calc forum).

Thanks to add '[Solved]' at beginning of your first post title (edit button) if your issue has been fixed.

[keme]

... i.e., both in the Result and the Else part you can have an IF() function call (instead of the string constants that I used in my example).

Note that nested IF() formulas easily grow to unmanageable complexity. Consider using stepwise calculation (each IF() level in a separate cell).
In many cases, a better solution is possible by using some xLOOKUP() function, or MATCH()/INDEX() combinations.
The CHOOSE() function is also sometimes useful.

[saidtomuch]

Sorry to appear so stupid but this appears to be a step to far for me. I will post what I am trying to formulate and see what comes back.

=IF
E1>B1 AND B2>C1=U-H
E1>B1 AND B2>E1=U-HC
E1>B1 AND B2>B1=U-CO
E1>B1 AND B2>D1=U-OL
E1>B1 AND B2<D1=U-L
E1<B1 AND B2>C1=D-H
E1<B1 AND B2>B1=D-HO
E1<B1 AND B2>E1=D-OC
E1<B1 AND B2>D1=D-CL
E1<B1 AND B2<D1=D-L

Once again sorry to be so uneducated but I do appreciate any feedback.

[RoryOF]

You should look up the functions keme mentioned in
http://wiki.services.openoffice.org/wik ... y_category

As he says, you might be wiser to use some differing functions rather than an IF/THEN/ELSE sequence.

[saidtomuch]

Just like to say thanks to you guys for your help today. In the end what I thought was right, it was all above me. Keme, I used your stepwise calculation and entered one if() in each cell, can't view it all at once but many thanks for your help.

[keme]

If it's not too late, there may be a "compact" solution. Formulas are logical tools, so explaining the logic behind your required evaluation makes it easier to create a formula. I might guess that cell C1 is always the largest and D1 always the smallest in the range B1:E1, and E1=B1 will never occur. Also I see four letters "HOCL" that seem to follow a pattern and encode something meaningful.

Based on that, this lengthy formula may give you what you need:

Code: Select all

=IF(E1>B1;"U-"&IF(B2>E1;"H";"")&IF(AND(B2<=C1;B2>B1);"C";"")&IF(AND(B2>B1;B2>D1);"O";"")&IF(B2<=B1;"L";"");"D-"&IF(B2>B1;"H";"")&IF(AND(B2<=C1;B2>E1);"O";"")&IF(AND(B2<=B1;B2>D1);"C";"")&IF(B2<=E1;"L";""))

It may be possible to cut it down more, but that depends on the context and the possible values in the "test set" (and their relative magnitudes).