MrExcel Publishing
Your One Stop for Excel Tips & Solutions

IF Statement trouble


Posted by AL on December 24, 2001 8:43 AM

I am trying to get the following if statement to work, but for some reason something is wrong. any help would be appreciated. Al
=IF(AM47<BU29,"low",IF(AM47>BU29<BV29,"good",IF(AM47>BV29<BW29,"average",IF(AM47>BW29<BX29,">average","excessive"))))


Posted by Al on December 24, 2001 8:56 AM

Posted by Aladin Akyurek on December 24, 2001 9:00 AM

Al --

I'm trying to understand the comparisons that you make:

AM47 less than BU29 --> low

AM47 greater than BU29 and AM47 less than BV29 --> good

AM47 greater than BV29 and AM47 less than BW29 --> average

AM47 greater than BW29 and AM47 less than BX29 --> above average

all else --> excessive

Is the above summary right?

Aladin

Posted by Al on December 24, 2001 9:21 AM

Yes the above is correct

AL

Posted by Aladin Akyurek on December 24, 2001 9:57 AM

Al --

[a]

=IF(AM47 < BU29,"low",IF(AM47 < BV29,"good",IF(AM47 < BW29,"average",IF(AM47 < BX29,"above average","excessive"))))

I hoped you'd say something about the cases when AM47 is equal to a cell to which it is compared to. The foregoing formula does not do the equality test, therefore wrong, but it's up to you to evaluate and decide.

If you add the equality test to the above formula, we get:

[b]

=IF(AM47<=BU29,"low",IF(AM47<=BV29,"good",IF(AM47<=BW29,"average",IF(AM47<=BX29,"above average","excessive"))))

If this one is correct, have a look at what follows which is intended to be identical to [b] in effect. If it meets your needs, I'd suggest using it:

[c]

=IF(AM47>=BX29,"excessive",INDEX({"low","good","average","above average"},MATCH(AM47,BU29:BX29)))

Aladin

===========

Posted by Aladin Akyurek on December 24, 2001 9:10 PM

Al, forget (c), it's either (a) or (b). -Aladin

Posted by Al on December 26, 2001 8:41 AM


Aladin
I was using [C] however if the percentage was too low I would get N/A, how can I eliminate the N/A