Nested If Function

[rationaloptimism]

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!

 

[Joe4]

Welcome to the Board!

You type the number 12 (twelve) instead of I2 for that condition:

[COLOR=#333333]IF([/COLOR][COLOR=#ff0000][B]1[/B][/COLOR][COLOR=#333333]2>0.6,"Slightly Right",

should be:
[/COLOR]

[COLOR=#333333]IF([/COLOR][COLOR=#ff0000][B]I[/B][/COLOR][COLOR=#333333]2>0.6,"Slightly Right",[/COLOR]

 

[rationaloptimism]

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]

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

 

[Eric W]

Welcome to the board.

You could do something like:

	I	J
Score
Far Left
Far Left
Center Left
Slightly Left
Centrist
Centrist
Slightly Right
Center Right
Far Right
Far Right
Far Right

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

[TD="align: right"][/TD]

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

[TD="align: center"]3[/TD]
[TD="align: right"]0.1[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]0.2[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]0.3[/TD]

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

[TD="align: center"]7[/TD]
[TD="align: right"]0.5[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]0.6[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]0.7[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]0.8[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]0.9[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]1[/TD]

</tbody>

Sheet5​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J2[/TH]
[TD="align: left"]=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"})[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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]

You are welcome.
Glad to help!