Nest If - What am I doing wrong...
"KS" wrote:
=IF(E2<3,"<3", IF(OR(E2=3,E2<=5), "3-5", IF(OR(E2=6,E2<=10),
"6-10", IF(OR(E2=11,E2<=15), "11-15", IF(OR(E2=16,E2<=20),
"16-20", "20")))))
[....]
What is wrong with the formula?
The short answer is: you should use AND() instead of OR().
However, the formula can be greatly simplified. At a minimum:
=IF(E2<3,"<3", IF(E2<=5, "3-5", IF(E2<=10, "6-10",
IF(E2<=15, "11-15", IF(E2<=20, "16-20", "20")))))
Even simpler and more-flexible/efficient formulas might also be possible.
But first, it would be helpful to know: (a) is E2 always an integer; and
(b) can E2 be negative?
----- original message -----
"KS" wrote in message
...
Hey all,
Hope your day is going better then mine. :)
Getting right to the point...
Here is the formula that goes in column G
=IF(E2<3,"<3",IF(OR(E2=3,E2<=5),"3-5",IF(OR(E2=6,E2<=10),"6-10",IF(OR(E2=11,E2<=15),"11-15",IF(OR(E2=16,E2<=20),"16-20","20")))))
data out of column E
11
15
3
20
-2
actual output of column G from formula
3-5
3-5
3-5
3-5
<3
output that SHOULD be coming into column G
11-15
11-15
3-5
16-20
<3
What is wrong with the formula? Why is it only looking at the first 2 Ifs?
Hope this makes sense, if you have any questions let me know. Any help
would
be great. Thanks in advance!
|