If statement

Posted by Frank on September 14, 2001 10:39 AM

Hi, i have the following problem

I have a value "X" wich I want to compare with "S" "T" and "I"

for example if x &LT; S then return "-" but in the same time he need to check if X isnt bigger then S and smaller then T (return "+") or bigger then T and smaller then I (return "++") or bigger then I (return "+++")

Posted by Juan Pablo on September 14, 2001 10:50 AM

The S, T and I values are in order ? meaning this always is true ?:

S &LT; T &LT; I ??

Juan Pablo

Posted by frank on September 14, 2001 10:59 AM

I mean that if X is not smaller then S then he has to see if X is >= S and smaller then T and when that isnt true then he has to see if X is >=T and smaller then I and if that isnt true then he has to see if X is bigger then I.

So one of them must be true and then there must be an output like I said.

Posted by Juan Pablo on September 14, 2001 12:29 PM

Yes, i understand that. But what i mean is that S is ALWAYS smaller than T, and T is ALWAYS smaller than I.... i'm not asking anything about X.... is just to simplify the formula.

Juan Pablo

Posted by Mark W. on September 17, 2001 12:52 PM

Suppose the values for X, S, T, and I are entered
into cells A1, B1, C1 and D1 respectively. Then
the array formula...

{=IF(AND(A1&LT;B1:D1),"-",REPT("+",MAX((A1>=B1:D1)*{1,2,3})))}

...can be used produce your desired results.

Note: This is an array formula which must be
entered using the Control+Shift+Enter key
combination. The outermost braces, {}, are not
entered by you -- they're supplied by Excel in
recognition of a properly entered array formula.

Posted by Mark W. on September 17, 2001 12:54 PM

Reposted Formula...

Suppose the values for X, S, T, and I are entered
into cells A1, B1, C1 and D1 respectively. Then
the array formula...

{=IF(AND(A1 &LT; B1:D1),"-",REPT("+",MAX((A1 >= B1:D1)*{1,2,3})))}

...can be used produce your desired results.