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

 

[Mettaya1]

This didn't work on my sheet Markmzz

 

[Mettaya1]

Thanks All that contributed here. I have solved my problem now.
Special mention to bschwartz and Michael M

 

[bschwartz]

That's not exactly an error in your condition statement. It's just the explanation for why blank cells are turning red. You could still format ratings of 0 with red and leave blanks unformatted, by either following my instruction above, or modifying the 0 is red formula to include a <>"" statement. For example, =AND(G2=0,G2<>"").

 

[markmzz]

This didn't work on my sheet Markmzz

What you got?

Could you post the formula that you used?

Markmzz

 

[Mettaya1]

what you got?

Could you post the formula that you used?

Markmzz

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

I also used in the rating side in Column B:
=IF(A3<>"",IF(ISNUMBER(A1),A2&" to "&A3,A2),A2&"+")

 

[markmzz]

This didn't work on my sheet Markmzz

What you got?

Could you post the formula that you used?

Markmzz

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

I also used in the rating side in Column B:
=IF(A3<>"",IF(ISNUMBER(A1),A2&" to "&A3,A2),A2&"+")

Sorry, but I'm talking about my formula of the post #7. Here it worked with your layout of post #1 and #5. Look at this:

Post #1 layout

Score	Rating				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
******	******	**	**	**	*************	*********

<colgroup><col width="37" style="width: 28pt; mso-width-source: userset; mso-width-alt: 1353;" span="2"> <col width="17" style="width: 13pt; mso-width-source: userset; mso-width-alt: 621;" span="3"> <col width="72" style="width: 54pt; mso-width-source: userset; mso-width-alt: 2633;"> <col width="52" style="width: 39pt; mso-width-source: userset; mso-width-alt: 1901;"> <tbody>
</tbody>

Formula for post #1 layout

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

Post #5 layout

Original Score	CoPTTM Score	Rating	CoPTTM Site Condition Rating		CoPTTM Score	Rating
0	0	10	High		26	4
1	1 to 6	9			18	6
6	6 to 11	8
11	11 to 16	7	Acceptable		51	0
16	16 to 21	6			35	3
21	21 to 26	5			39	2
26	26 to 31	4	Needs Improvement		50	1
31	31 to 36	3			0	10
36	36 to 41	2			3	9
41	41 to 51	1			8	8
51	51+	0	Dangerous		12	7
*******	*******	******	***************************	**	*************	******

<colgroup><col width="42" style="width: 32pt; mso-width-source: userset; mso-width-alt: 1536;"> <col width="45" style="width: 34pt; mso-width-source: userset; mso-width-alt: 1645;"> <col width="37" style="width: 28pt; mso-width-source: userset; mso-width-alt: 1353;"> <col width="106" style="width: 80pt; mso-width-source: userset; mso-width-alt: 3876;"> <col width="17" style="width: 13pt; mso-width-source: userset; mso-width-alt: 621;"> <col width="44" style="width: 33pt; mso-width-source: userset; mso-width-alt: 1609;"> <col width="37" style="width: 28pt; mso-width-source: userset; mso-width-alt: 1353;"> <tbody>
</tbody>

Formula for post #5 layout

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

Markmzz

 

[Mettaya1]

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

I got this when I tried it

Table					Original Score	Rating
0	10				26	G2-> =IF($F2="","",LOOKUP($F2,--LEFT(SUBSTITUTE($A$2:$A$12,"-"," "),2),$B$2:$B$12))
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>

The code just appeared in cell G2, I assume thats where it should be going... just couldn't get it to work. But looked like it would have been simpler.

 

[markmzz]

I got this when I tried it

Table					Original Score	Rating
	10				26	G2-> =IF($F2="","",LOOKUP($F2,--LEFT(SUBSTITUTE($A$2:$A$12,"-"," "),2),$B$2:$B$12))
1-5	9				18	6
6-10	8
11-15	7				51
16-20	6				35	3
21-25	5				39	2
26-30	4				50	1
31-35	3					10
36-40	2				3	9
41-50	1				8	8
51+					12	7

<tbody>
</tbody>

The code just appeared in cell G2, I assume thats where it should be going... just couldn't get it to work. But looked like it would have been simpler.

Try only this:

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

PS: without G2->

Markmzz

 

[Mettaya1]

Try only this:

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

PS: without G2->

Markmzz

That worked... thanks heaps Markmzz

 

[markmzz]

That worked... thanks heaps Markmzz

You're welcome.

Markmzz