If with Multiple Conditions

[msharma864512]

I am trying to write a formula to return some text matching certain conditions.

Details are in the attached spreadsheet.

 

[AliGW]

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

 

[msharma864512]

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

Thank you Ali

 

[AliGW]

You're welcome!

 

[GraH - Guido]

Perhaps a (shorter) alternative could also do it
=IF(C7=0,"",LOOKUP(BIN2DEC((A7<B7)*1&(C7<D7)*1),{0,1,2,3},{"Sustain","Increase","Limited","Minimum"}))

 

[bosco_yip]

Or....

=IF(C7=0,"",INDEX({"Sustain";"Increase";"Limited";"Minimum"},BIN2DEC(0+(A7<B7)&0+(C7<D7))+1))

Regards
Bosco

 

[GraH - Guido]

Damn', you've done that before, Bosco. I already forgot that trick. Should remember it.

 

[pecoflyer]

Even shorter =IF(C7=0,"",INDEX({"Sustain";"Increase";"Limited";"Minimum"},((A7<B7)+(C7<D7))+1))

 

[GraH - Guido]

Even shorter =IF(C7=0,"",INDEX({"Sustain";"Increase";"Limited";"Minimum"},((A7<B7)+(C7<D7))+1))

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

 

[Peter Bartholomew]

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

I think this should be
=IF(C7=0,"",INDEX({"Sustain";"Increase";"Limited";"Minimum"},
(2*(A7<B7)+(C7<D7))+1))

 

[msharma864512]

Thank u all, i am very thrilled to see so many responses for one query, now i am really confused which one to choose.

Thank u one again

 

[AliGW]

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.