Having trouble with a long Nested IF Statement - HELP!

[Mettaya1]

F Original Score	G Rating
26	4
18	6

<colgroup><col style="mso-width-source:userset;mso-width-alt:4022; width:83pt" span="2" width="110"> </colgroup><tbody>
</tbody>

I have created a nested IF statement to average out the original scores to the table below

Original Score	Rating
0	10
1-5	9
6-10	8
11-15	7
16-20	6
21-25	5
26-30	4
31-35	3
36-40	2
41-50	1
51+	0

<colgroup><col><col span="2"></colgroup><tbody>
</tbody>

=IF(F3=0,10,IF(F3="", "",IF(AND(F3>=1,F3<=5),9,IF(AND(F3>=6,F3<=10),8,IF(AND(F3>=11,F3<=15),7,IF(AND(F3>=16,F3<=20),6,IF(AND(F3>=21,F3<=25),5,IF(AND(F3>=26,F3<=30),4,IF(AND(F3>=31,F3<=35),3,IF(AND(F3>=36,F3<=40),2,IF(AND(F3>=41,F3<=50),1,IF(AND(F3>50,F3<=1500),0))))))))))))

It works fine except that if there is a blank cell in F I get a rating of 10. I just want it to give a blank if the original score was blank instead of 10.

Any help would be appreciated.

Regards Mettraya1

 

[bschwartz]

If you change your data structure a bit, you can use a simple VLOOKUP.

	A	B
1	Orig. Score	Rating
2	0	10
3	1	9
4	6	8
5	11	7
6	16	6
7	21	5
8	26	4
9	31	3
10	36	2
11	41	1
12	51	0

<tbody>
</tbody>

	F	G
1	Orig. Score	Rating
2	26	4
3	18	6

<tbody>
</tbody>

In G2 (and fill down): =VLOOKUP(F2,$A$2:$B$12,2)

 

[bschwartz]

If you like, you can also add a column explaining the ranges (and if you like hide column A). For example:

	A	B	C
1	Orig. Score	Range	Rating
2	0	0	10
3	1	1 to 6	9
4	6	6 to 11	8
5	11	11 to 16	7
6	16	16 to 21	6
7	21	21 to 26	5
8	26	26 to 31	4
9	31	31 to 36	3
10	36	36 to 41	2
11	41	41 to 51	1
12	51	51+	0

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

This formula in cell B2 and fill down: =IF(A3<>"",IF(ISNUMBER(A1),A2&" to "&A3,A2),A2&"+")

 

[Michael M]

I'd definitely go with bschwartz sugestion, but if you can't for some reason, try

=IF(F3="","",IF(F3=0,10,IF(F3<=5,9,IF(F3<=10,8,IF(F3<=15,7,IF(F3<=20,6,IF(F3<=25,5,IF(F3<=30,4,IF(F3<=35,3,IF(F3<=40,2,IF(F3<=50,1,IF(F3<=1500,0,""))))))))))))

 

[Mettaya1]

If you like, you can also add a column explaining the ranges (and if you like hide column A). For example:

	A	B	C
1	Orig. Score	Range	Rating
2	0	0	10
3	1	1 to 6	9
4	6	6 to 11	8
5	11	11 to 16	7
6	16	16 to 21	6
7	21	21 to 26	5
8	26	26 to 31	4
9	31	31 to 36	3
10	36	36 to 41	2
11	41	41 to 51	1
12	51	51+	0

<tbody>
</tbody>

This formula in cell B2 and fill down: =IF(A3<>"",IF(ISNUMBER(A1),A2&" to "&A3,A2),A2&"+")

Tried this suggestion and got the following

A	B	C	D	E	F	G
Original Score	CoPTTM Score	Rating	CoPTTM Site Condition Rating		CoPTTM Score	Rating
0	0	10	High		26	26 to 31	=VLOOKUP(F2,$A$2:$B$12,2)
1	1 to 6	9		18	16 to 21
6	6 to 11	8
11	11 to 16	7	Acceptable
16	16 to 21	6
21	21 to 26	5
26	26 to 31	4	Needs Improvement
31	31 to 36	3
36	36 to 41	2
41	41 to 51	1
51	51+	0	Dangerous
					Average	#DIV/0!

<colgroup><col><col span="2"><col><col><col span="2"></colgroup><tbody>
</tbody>

Not sure why its not working. Put the function in the cell to the right

 

[bschwartz]

Sorry, when I made the second suggestion, I should have updated the formula.

Use this: =VLOOKUP(G2,$A$2:$C$12,3)

 

[markmzz]

Another way (with your table):

Layout

Table					Original Score	Rating
0	10				26	4
1-5	9				18	6
6-10	8
11-15	7				51	0
16-20	6				35	3
21-25	5				39	2
26-30	4				50	1
31-35	3				0	10
36-40	2				3	9
41-50	1				8	8
51+	0				12	7
******	******	**	**	**	*************	*********

<tbody>
</tbody>

Formula

G2-> =IF($F2="","",LOOKUP($F2,--LEFT(SUBSTITUTE($A$2:$A$12,"-"," "),2),$B$2:$B$12))

Markmzz

 

[Mettaya1]

This appears to work well.

However am using conditional formatting in the G Column as follows
Rating of 10 -8 = Green
Rating of 7-5 = Yellow
Rating of 4-1 = Orange
Rating of 0 = Red

Your calculation formats the blank cells as Red, which is unfortunate. Otherwise appears to work well.
bschwartz just wouldn't work correctly for some reason but the B2 Range function was great.

 

[bschwartz]

Why don't you try adding another conditional formatting rule (on the top of the list), that applies no formatting if

=G2=""

?

 

[Mettaya1]

I found a simple error in my condition statement. If you put condition as =0 or course it will also include blank cells.... dah.
But thanks bschwartz for your post... it made me look and see my mistake.