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
