Having trouble with a long Nested IF Statement - HELP!

M

Mettaya1

New Member
Joined
Oct 8, 2013
Messages
20
4
6

<colgroup><col style="mso-width-source:userset;mso-width-alt:4022; width:83pt" span="2" width="110"> </colgroup><tbody>
[TD="class: xl69, width: 110"] F

Original Score
[/TD]
[TD="class: xl70, width: 110"] G

Rating
[/TD]

[TD="class: xl68"]26[/TD]

[TD="class: xl65"]18
[/TD]

</tbody>

I have created a nested IF statement to average out the original scores to the table below
[TABLE="width: 358"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD]Original Score
[/TD]
[TD]Rating[/TD]
[/TR]
[TR]
[/TR]
[TR]
[TD]0[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]1-5[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]6-10[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]11-15[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]16-20[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]21-25[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]26-30[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]31-35[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]36-40[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]41-50[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]51+[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

=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.:confused:

Regards Mettraya1
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Sort by date Sort by votes
If you change your data structure a bit, you can use a simple VLOOKUP.

AB
Orig. ScoreRating

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]0[/TD]

</tbody>

FG
Orig. ScoreRating

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]6[/TD]

</tbody>

In G2 (and fill down): =VLOOKUP(F2,$A$2:$B$12,2)
 
Upvote 0
If you like, you can also add a column explaining the ranges (and if you like hide column A). For example:

ABC
Orig. ScoreRangeRating

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: center"]1 to 6[/TD]
[TD="align: right"]9[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: center"]6 to 11[/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]11[/TD]
[TD="align: center"]11 to 16[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]16[/TD]
[TD="align: center"]16 to 21[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]21[/TD]
[TD="align: center"]21 to 26[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]26[/TD]
[TD="align: center"]26 to 31[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]31[/TD]
[TD="align: center"]31 to 36[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]36[/TD]
[TD="align: center"]36 to 41[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]41[/TD]
[TD="align: center"]41 to 51[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]51[/TD]
[TD="align: center"]51+[/TD]
[TD="align: right"]0[/TD]

</tbody>

This formula in cell B2 and fill down: =IF(A3<>"",IF(ISNUMBER(A1),A2&" to "&A3,A2),A2&"+")
 
Upvote 0
I'd definitely go with bschwartz sugestion, but if you can't for some reason, try

Code: 
=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,""))))))))))))
 
Upvote 0
bschwartz said:
If you like, you can also add a column explaining the ranges (and if you like hide column A). For example:

ABC
Orig. ScoreRangeRating

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: center"]1 to 6[/TD]
[TD="align: right"]9[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: center"]6 to 11[/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]11[/TD]
[TD="align: center"]11 to 16[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]16[/TD]
[TD="align: center"]16 to 21[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]21[/TD]
[TD="align: center"]21 to 26[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]26[/TD]
[TD="align: center"]26 to 31[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]31[/TD]
[TD="align: center"]31 to 36[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]36[/TD]
[TD="align: center"]36 to 41[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]41[/TD]
[TD="align: center"]41 to 51[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]51[/TD]
[TD="align: center"]51+[/TD]
[TD="align: right"]0[/TD]

</tbody>

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

Tried this suggestion and got the following

[TABLE="width: 736"]
<colgroup><col><col span="2"><col><col><col span="2"></colgroup><tbody>[TR]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[TD="align: center"]F
[/TD]
[TD="align: center"]G
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Original Score
[/TD]
[TD="align: center"]CoPTTM Score
[/TD]
[TD="align: center"]Rating[/TD]
[TD="align: center"]CoPTTM Site Condition Rating[/TD]
[TD="align: center"][/TD]
[TD="align: center"]CoPTTM Score[/TD]
[TD="align: center"]Rating[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]0
[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]10[/TD]
[TD]High
[/TD]
[TD="align: center"] [/TD]
[TD="align: center"]26[/TD]
[TD="align: center"]26 to 31
[/TD]
[TD="align: center"]=VLOOKUP(F2,$A$2:$B$12,2)
[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]1 to 6[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"] [/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]16 to 21[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]6 to 11[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]11 to 16[/TD]
[TD="align: center"]7[/TD]
[TD]Acceptable[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD="align: center"]16 to 21[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]21[/TD]
[TD="align: center"]21 to 26[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"] [/TD]
[TD="align: center"]
[/TD]
[TD="align: center"] [/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]26[/TD]
[TD="align: center"]26 to 31[/TD]
[TD="align: center"]4[/TD]
[TD]Needs Improvement[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]31[/TD]
[TD="align: center"]31 to 36
[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"]
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]36[/TD]
[TD="align: center"]36 to 41[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]41[/TD]
[TD="align: center"]41 to 51[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]51[/TD]
[TD="align: center"]51+[/TD]
[TD="align: center"]0[/TD]
[TD]Dangerous[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"][/TD]
[TD="align: center"] [/TD]
[TD="align: center"]Average[/TD]
[TD="align: center"]#DIV/0![/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

Not sure why its not working. Put the function in the cell to the right:confused:
 
Upvote 0
Sorry, when I made the second suggestion, I should have updated the formula.

Use this: =VLOOKUP(G2,$A$2:$C$12,3)
 
Upvote 0
Another way (with your table):

Layout

[TABLE="width: 188"]
<tbody>[TR]
[TD="class: xl63, width: 37, bgcolor: transparent"]Table
[/TD]
[TD="class: xl63, width: 37, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 17, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 17, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 17, bgcolor: transparent"][/TD]
[TD="class: xl63, width: 72, bgcolor: transparent"]Original Score
[/TD]
[TD="class: xl63, width: 52, bgcolor: transparent"]Rating
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]0
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]10
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]26
[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]4
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]1-5
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]9
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]18
[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]6
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]6-10
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]8
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]11-15
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]7
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]51
[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]0
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]16-20
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]6
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]35
[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]3
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]21-25
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]5
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]39
[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]2
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]26-30
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]4
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]50
[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]1
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]31-35
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0
[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]10
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]36-40
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]9
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]41-50
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]8
[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]8
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]51+
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]0
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]12
[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]7
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]******
[/TD]
[TD="class: xl63, bgcolor: transparent"]******
[/TD]
[TD="class: xl63, bgcolor: transparent"]**
[/TD]
[TD="class: xl63, bgcolor: transparent"]**
[/TD]
[TD="class: xl63, bgcolor: transparent"]**
[/TD]
[TD="class: xl63, bgcolor: transparent"]*************
[/TD]
[TD="class: xl63, bgcolor: transparent"]*********
[/TD]
[/TR]
</tbody>[/TABLE]


Formula

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

Markmzz
 
Upvote 0
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.
 
Upvote 0
Why don't you try adding another conditional formatting rule (on the top of the list), that applies no formatting if
Code: 
=G2=""
?
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

wizau
  • Solved
Nested IF statements, or a better way?
Replies
4
Views
245
wizau
wizau
S
  • Question Question
Formula for Equity Vesting
Replies
6
Views
1K
Dave Patton
D
Z
  • Question Question
Massive Nested IF Statement Help Needed
Replies
1
Views
366
Zutalores
Z
A
  • Question Question
Attempting to auto generate ID names based on uncertain parameters
Replies
1
Views
232
Rijnsent
Rijnsent
J
  • Question Question
Removing blank rows based on cell value
Replies
2
Views
247
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top