# Nested If Function

#### rationaloptimism

##### New Member
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

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

#### Joe4

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
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?

#### rationaloptimism

##### New Member
Joe! Thank you! Great catch! That fixed it. I'm impressed! Thank you.

#### Eric W

##### MrExcel MVP
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

</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"})

</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.

You are welcome.

Replies
0
Views
49
Replies
1
Views
118
Replies
4
Views
209
Replies
3
Views
255
Replies
2
Views
164

1,181,607
Messages
5,930,873
Members
436,764
Latest member

### 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.

### Which adblocker are you using?

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

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