Hi!
Here's one way:
Create a table that lists your criteria designations:
1a
1b
1c
...
...
2d
In the adjacent column enter these formulas as they
correspond to each criteria:
=AND(A10=A2,A10=B2,A10=C2)
=AND(A10=A2,A10=B2,A10<C2)
=AND(A10=A2,A10<B2,A10=C2)
=AND(A10=A2,A10<B2,A10<C2)
=AND(A10<A2,A10=B2,A10=C2)
=AND(A10<A2,A10=B2,A10<C2)
=AND(A10<A2,A10<B2,A10=C2)
=AND(A10<A2,A10<B2,A10<C2)
In this example I'm using A10 as the comparison cell and
A2,B2 and C2 to hold "R" , "HR" and "%", respectively.
Now, to return "D" use this formula:
=IF(OR(A10="",A2="",B2="",C2=""),"",INDEX(J2:J9,MA TCH
(TRUE,I2:I9,0)))
Whe
J2:J9 = criteria designations
I2:I9 = corresponding criteria formula
Biff
-----Original Message-----
I n1ceed to evaluate the contents of three cells and
return one of 8
choices in a fourth cell. The contents of the cells to
be evaluated are
numbers and are compared to a given number as either "<"
or "=".
The choices to return are 1a thru 1d or 2a thru 2d.
R HR % D
5 8 8 1a etc.
I have nested IF and AND functions, but can't figure out
how to get past
the 7 nested functions limitation.
The formula works for 7 of the sets of conditions, but
not the 8th.
The criteria a
1a - high R, high HR, high %
1b - high R, high HR, low %
1c - high R, low HR, high %
1d - high R, low HR, low %
2a - low R, high HR, high %
2b - low R, high HR, low %
2c - low R, low HR, high %
2d - low R, low HR, low %
Thanks in advance for any thoughts or suggestions.
Regards,
Luke
.
|