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!

Just start at the top and work you way down
=if(e320,""20",if(e315,"16:20",etc
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"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!

It should be AND

=IF(E2<3,"<3",IF(AND(E2=3,E2<=5),"3-5",
IF(AND(E2=6,E2<=10),"6-10",
IF(AND(E2=11,E2<=15),"11-15",
IF(AND(E2=16,E2<=20),"16-20","20")))))

If this post helps click Yes
---------------
Jacob Skaria


"KS" wrote:

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!

"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!

1) Your ORs should be ANDs
IF(OR(x 3, x<5, "x is between 3 and 5", "some other value" NO
IF(OR(x 3, x<5, "x is either greater than 3 or less than 5", "some other
value" YES
Could be 2 (less than 5) , 4 (less than 5), 22 (greater than 3) etc (not
very informative)

IF(AND(x 3, x<5,"x is between 3 and 5","some other value" YES
Be sure to study this for future problems


2) but you go not need the AND
=IF(x <3, "X is less than 3", if(X = 5, "x is 3 to 5....
The values lower than 3 are 'trapped' by the first test and do not need to
be test again

So:

=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")))))

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"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!

You can acheive the same using a LOOKUP() function...Easy to edit

=LOOKUP(E2,{0,3,6,11,16,21},{"<3","3-5","6-10","11-15","16-20","20"})


If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

It should be AND

=IF(E2<3,"<3",IF(AND(E2=3,E2<=5),"3-5",
IF(AND(E2=6,E2<=10),"6-10",
IF(AND(E2=11,E2<=15),"11-15",
IF(AND(E2=16,E2<=20),"16-20","20")))))

If this post helps click Yes
---------------
Jacob Skaria


"KS" wrote:

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!

This is perfect!!! I should have known that...THANK YOU!!!!

"Jacob Skaria" wrote:

It should be AND

=IF(E2<3,"<3",IF(AND(E2=3,E2<=5),"3-5",
IF(AND(E2=6,E2<=10),"6-10",
IF(AND(E2=11,E2<=15),"11-15",
IF(AND(E2=16,E2<=20),"16-20","20")))))

If this post helps click Yes
---------------
Jacob Skaria


"KS" wrote:

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!

would be a lot easier to simply enter:
A B
0 <3
3 3-5
6 6-10
11 11-15
16 16-20
21 20

in a table, then you only need to do =VLOOKUP(E2,A:B,2)

"KS" wrote:

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!