Nested if problem in excel

August 12, 2010 at 06:05:39
Specs: Windows 7
pls help.. what is wrong of my condition?=IF(A1=100,"1.00",IF(A1=99,"1.00",IF(A1<=98,"1.25",IF(A1=97,"1.25",IF(A1=96,"1.25",IF(A1=95,"1.25",IF(A1=94,"1.50",IF(A1=93,"1.50",IF(A1=92,"1.50",IF(A1=91,"1.75",IF(A1=90,"1.75",IF(A1=89,"1.75",IF(A1=88,"2.00",IF(A1=87,"2.00",IF(A1=86,"2.00",IF(A1=85,"2.25",IF(A1=84,"2.25",IF(A1=83,"2.25",IF(A1=82,"2.50",IF(A1=81,"2.50",IF(A1=80,"2.50",IF(A1=79,"2.75",IF(A1=78,"2.75",IF(A1=77,"3.00",IF(A1=76,"3.00",IF(A1=75,"3.00",IF(A1<74.5,"5.00"))))))))))))))))))))))))))) it seems that it is wrong because it is a grade point.. when i entered 75, 1.25 was appeared instead of 3.00.

See More: Nested if problem in excel

Report •


#1
August 12, 2010 at 07:38:56
I don't have Excel 2007, so I'm limited to only 7 nested IF's so I can't load your expression to check but, why not use a =VLOOKUP() instead?

MIKE

http://www.skeptic.com/


Report •

#2
August 12, 2010 at 08:01:38
how can i do dat? pls help me.. tis' isn't excel 2007.. it's a excel 2003.

Report •

#3
August 12, 2010 at 08:37:02
First, you should know that in Excel 2003 you can have only 7 nested IF statments.
Excel 2007 allows for over 60.

Try this:

On Sheet number 2 of your workbook add:

	A	B
 1)	100	1.00
 2)	99	1.00
 3)	98	1.25
 4)	97	1.25
 5)	96	1.25
 6)	95	1.25
 7)	94	1.50
 8)	93	1.50
 9)	92	1.50
10)	91	1.75
11)	90	1.75
12)	89	1.75
13)	88	2.00
14)	87	2.00
15)	86	2.00
16)	85	2.25
17)	84	2.25
18)	83	2.25
19)	82	2.50
20)	81	2.50
21)	80	2.50
22)	79	2.75
23)	78	2.75
24)	77	3.00
25)	76	3.00
26)	75	3.00

On sheet number 1 of your workbook, in cell B1 put the formula:

=IF(ISBLANK(A1),"",IF(A1<75,"5.00",VLOOKUP(A1,Sheet2!A1:B26,2,FALSE)))

This says if Cell A1 is Blank, do nothing,
if cell A1 is less than 75, make it 5.00,
if cell A1 is not less than 75 then lookup the grade in our table on sheet number 2 and return the corresponding number.

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
August 12, 2010 at 08:56:23
Mike is correct is saying that's it probably better to use a VLOOKUP function, but to answer your original question as to why your formula doesn't work, think about how an IF statement does it's thing.

It starts at the beginning and checks each condition. As soon as it finds a TRUE condition, it returns the value associated with that condition and stops checking.

Look at your third condition:

IF(A1<=98,"1.25", ...

Since 75 is less than 98, that condition is TRUE, so your formula returns 1.25.

The trick is to turn the IF around and start with the lowest value first.

In addition, you don't have check every single value. Since you have "breakpoints" - 74.5, 77, 79, etc. - you can use these to set your conditions.

I can't check this many IF's in 2003, but I believe that this should work for you:

=if(a1<74.5,5,if(a1<78,3,if(a1<80,2.75,if(a1<83,2.5,
if(a1<86,2.25,if(a1<89,2,if(a1<92,1.75,if(a1<95,1.5,
if(a1<99,1.25,1)))))))))

Format the cell to show 2 decimal points and you'll get 5.00, 3.00 etc.


Report •

#5
August 12, 2010 at 09:05:26
DerbyDad03, you've got 8 nested IF's........

MIKE

http://www.skeptic.com/


Report •

#6
August 12, 2010 at 09:14:32
I know...that's why I said I can't test it in 2003, but that it should work in 2007.

I deleted the last IF and tested it 2003, so the first 7 work. I see no reason why the 8th shouldn't, but as I said I can't test it.

Besides, this was more of a lesson on how to construct an IF statement in the most efficient manner since we agree that VLOOKUP is probably better.

(More on that in my next response.)

EDIT: Hmmm...something doesn't make sense here. I just noticed that the OP said it was 2003. How could he have gotten any answer from the formula in his OP? It should have given him an error as soon as he hit Enter.

Something is amiss...


Report •

#7
August 12, 2010 at 09:27:03
While Mike's VLOOKUP setup will work fine, there is another option.

The range_lookup argument can be used to shorten both the formula and the table_array. By setting that argument to TRUE, VLOOKUP will look for an exact match of the lookup_value, but will use the next lowest value if it can't find a match.

By using the table below along with this formula, I believe you'll get the same results.

=VLOOKUP(A1,Sheet2!$A$1:$B$10,2,TRUE)

	A	B
1	0	5
2	75	3
3	78	2.75
4	80	2.5
5	83	2.25
6	86	2
7	89	1.75
8	92	1.5
9	95	1.25
10	99	1

I'm not saying that this is any better than Mike's table which includes every combination - in fact it might be a little more confusing. I'm simply offering a formula that shows how the range_lookup argument works in case it's ever needed in the future.


Report •

#8
August 13, 2010 at 02:48:42
thanks for the reply.. i appreciated each replies. but our teacher told us to do that with if statements in excel. now, i'm wandering how can i debug the errors.

Report •

#9
August 13, 2010 at 04:30:59
First - you need to tell us if you are using Excel 2003 or a later version.

The fact that you got an answer (1.25) from the formula you posted in your OP tells us that you have at least 2007. If not, you would have gotten an error as soon as you hit Enter because 2003 and earlier will not support that many IF's. It won't just give you the wrong answer, it won't even accept the formula.

Second, if you have 2007, then the IF formula I suggested in Response # 4 should work for you.

Third, tell your teacher to call me. She needs to learn that VLOOKUP is the better solution for this problem. It's easier to maintain should changes need to be made and it's a much more elegant solution that expands the student's knowledge beyond the basic IF statement.

Feel free to show him/her this post.


Report •


Ask Question