Nested IF function to band data

Question

Hey,

I'll confess to not being the smartest on excel but I'm not sure why my IF functin is coming up with an error.

The function;

=IF(P2>-30,000,IF(P2>0,IF(P2>30,000,IF(P2>60,000,IF(P2>90,000,IF(P2>120,000,"120,000+","120,000 - 90,000"),"90,000 - 60,000"),"60,000 - 30,000"),"30,000 - 0"),"0- -30,000"),"LESS THAN -30,000")

Excel keeps telling me that there are too many arguements in the function and highlighting the 000" part of the 90,000 in the 'Value if false' part of the areguement.

If there is a better way of banding data then that would be great too!!

Thanks.

Answer 1

On Thu, 19 Aug 2010 09:10:56 +0000, Kimberelyrichard wrote:

Hey,

I'll confess to not being the smartest on excel but I'm not sure why my IF functin is coming up with an error.

The function;

=IF(P2>-30,000,IF(P2>0,IF(P2>30,000,IF(P2>60,000,IF(P2>90,000,IF(P2>120,000,"120,000+","120,000 - 90,000"),"90,000 - 60,000"),"60,000 - 30,000"),"30,000 - 0"),"0- -30,000"),"LESS THAN -30,000")

Excel keeps telling me that there are too many arguements in the function and highlighting the 000" part of the 90,000 in the 'Value if false' part of the areguement.

If there is a better way of banding data then that would be great too!!

Thanks.

The reason for your error is that you seem to have overlooked the fact

that a comma separates arguments.  You are using commas not only to

separate arguments, but also to separate parts of numbers.

e.g.

Your line:

=IF(P2>-30,000,IF(P2>0,...

becomes

    if

      (logical test)  P2 > -30

     (if true)    000  (which would get displayed as 0)

    (if false)    If(P2>0)

So what you probably want is:

=IF(P2>-30000,IF(P2>0,IF(P2>30000,IF(P2>60000,

IF(P2>90000,IF(P2>120000,"120,000+","120,000 - 90,000"),

"90,000 - 60,000"),"60,000 - 30,000"),"30,000 - 0"),"0- -30,000"),

"LESS THAN -30,000")

If what you are trying to do is look at P2, determine if the value

falls within a certain range, and then output that range, VLOOKUP

would be more easily maintained.

Formula:

=VLOOKUP(P2,G1:H7,2)

G1:H7

-1.00E+307    LESS THAN -30,000

-30000        0 - - 30,000

0        30,000 -  0

30000        60,000 -  30,000

60000        90,000 -  60,000

90000        120,000 -  90,000

120000        120,000+

Answer 2

Yeah it's what I want the formula to return if the number in cell P2 is in that band due to the conditions.

Answer 3

could you explain what are you looking for or what your formula will determine

what is this

,"120,000+","120,000 - 90,000"),", is it a text you want to print