• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

If with Multiple Conditions

This should do it:

=IF(AND(A7>B7,C7>D7),"Sustain",IF(AND(A7>B7,C7<D7,C7>0),"Increase",IF(AND(A7<B7,C7>D7),"Limited",IF(AND(A7<B7,C7<D7,C7>0),"Minimum",""))))
 
pecoflyer said:
Even shorter =IF(C7=0,"",INDEX({"Sustain";"Increase";"Limited";"Minimum"},((A7<B7)+(C7<D7))+1))
Click to expand...
That seems to return a different and incorrect result, I'm afraid.

+ There was a typo in my formula (I use EU settings and "," is ";" or "." is "," and vice versa, but not always it turns out).
Corrected: =IF(C7=0,"",LOOKUP(BIN2DEC((A7<B7)*1&(C7<D7)*1),{0;1;2;3},{"Sustain";"Increase";"Limited";"Minimum"}))
 
If the possible results are arranged as a 2x2 array
'Assessment': ={"Minimum","Limited";"Increase","Sustain"}
the two conditions
'HighScore': = 1 + (Table1[@Score]>Table1[@[Average Score]])
'HighTER': = 1 + (Table1[@TER]>Table1[@[Average TER]])
can be applied independently using the worksheet formula
= IF( [@TER], INDEX( Assessment, HighScore, HighTER ), "")

=IF(C7=0,"",INDEX({"Sustain";"Increase";"Limited";"Minimum"},((A7<B7)+(C7<D7))+1))
Click to expand...

I think this should be
=IF(C7=0,"",INDEX({"Sustain";"Increase";"Limited";"Minimum"},
(2*(A7<B7)+(C7<D7))+1))
 
Choose whichever one suits you best of the ones you understand. This way you will be able to adapt in the future.

It's as long as it is short: they all do the same thing.
 
You must log in or register to reply here.
Back
Top