Nested If Function

rationaloptimism

New Member
Joined
Nov 17, 2017
Messages
3
I have a 2 column table with a list of items, each getting a score from 0 to 1. I'm trying to create a third column that will generate one of 7 dfferent text values, each corresponding to a range of possible scores. Here's the formula I have:

=IF(I2>0.8,"Far Right",IF(I2>0.7,"Center Right",IF(12>0.6,"Slightly Right",IF(I2>0.4,"Centrist",IF(I2>0.3,"Slightly Left",IF(I2>0.2,"Center Left","Far Left"))))))

I thought that was the right way to do it, but it's not working. The formula returns "Slightly Right" for all values. What am I doing wrong? Is there a tweak needed to my formula? Is there a better way to do this using If formulas? Or do I need to take a different approach altogether to accomplish my goal?

Thank you!
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,125
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

You type the number 12 (twelve) instead of I2 for that condition:
Code:
[COLOR=#333333]IF([/COLOR][COLOR=#ff0000][B]1[/B][/COLOR][COLOR=#333333]2>0.6,"Slightly Right",
should be:
[/COLOR]
Code:
[COLOR=#333333]IF([/COLOR][COLOR=#ff0000][B]I[/B][/COLOR][COLOR=#333333]2>0.6,"Slightly Right",[/COLOR]
 

rationaloptimism

New Member
Joined
Nov 17, 2017
Messages
3
Hmm. It looks like it's just treating the third option as the final option. So if the number is higher than .8 it will say Far Right, if it's higher than .7 it will say Center Right, but for any other numbers it will just return the third option. But I thought Excel could handle 7 options? How do I do this?
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,683
Welcome to the board.

You could do something like:

IJ
1Score
20Far Left
30.1Far Left
40.2Center Left
50.3Slightly Left
60.4Centrist
70.5Centrist
80.6Slightly Right
90.7Center Right
100.8Far Right
110.9Far Right
121Far Right

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

Worksheet Formulas
CellFormula
J2=LOOKUP(I2,{0,0.2,0.3,0.4,0.6,0.7,0.8},{"Far Left","Center Left","Slightly Left","Centrist","Slightly Right","Center Right","Far Right"})

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>



This is the equivalent of your formula, with >= instead of =. You could change the values in it to .2001, etc. to get it the same as yours.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,125
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
Glad to help!
 

Forum statistics

Threads
1,148,108
Messages
5,744,876
Members
423,907
Latest member
zerocool88

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
Top