Excel 2000: Nested IF functions - I need to nest more than 7!

[LINSEY]

How can I get this formula to work if Excel will only let me use 7 nested functions?????

=IF(S2>1.8,&quot;+&quot;,IF(S2=1.8,&quot;9&quot;,IF(S2>1.4,&quot;9&quot;,IF(S2>1,&quot;8&quot;,IF(S2>0.6,&quot;7&quot;,IF(S2>0.2,&quot;6&quot;,IF(S2>-0.2,&quot;5&quot;,IF(S2>-0.6,&quot;4&quot;,IF(S3>-1,&quot;3&quot;,IF(S3>-1.4,&quot;2&quot;,IF(S3>-1.8,&quot;1&quot;,IF(S3<=-1.8,&quot;-&quot)))))))))))

 

[luceze]

One way to have more than 7 tests is to concatenate two if statements.
Example:
=IF(A1=1,&quot;Yes&quot;,&quot;&quot&IF(A1=2,&quot;No&quot;,&quot;&quot

You can then have 7 tests per if statement.

HTH,
Eric

 

[carrr]

See thread68-487916

&quot;'Tis an ill wind that blows no minds.&quot; - Malaclypse the Younger

 

[Zathras]

Here is a formula that you could use:
[blue]

=IF(S2>0.2,IF(S2>1,IF(S2>1.4,IF(S2>1.8,"+","9"),"8"),IF(S2>0.6,"7","6")),IF(S2>-1.4,IF(S2>-0.2,"5",IF(S2>-0.6,"4",IF(S2>-1,"3","2"))),IF(S2>-1.8,"1","-")))

[/color]
But a better way would be to use VLOOKUP. If you set up a lookup table like this:
[blue]

-99999  '-
-1.8    '1
-1.4    '2
-1      '3
-0.6    '4
-0.2    '5
0.2     '6
0.6     '7
1       '8
1.4     '9
1.8     '+

[/color]
(I put mine in W1:X11). Then you can simply use this:
[blue]

=VLOOKUP(S2,$W$1:$X$11,2)

[/color]
Much simpler to set up and even simpler to change when it becomes necessary. For example, to add more ranges, or change the definitions.

 

[bluedragon2]

One way is to have a table that lists your break points and options then do a vlookup on the table.

Another way is:

=IF(S2>1.8,&quot;+&quot;,&quot;&quot&IF(S2=1.8,&quot;9&quot;,&quot;&quot&IF(AND(S2>1.4,S2<1.8),&quot;9&quot;,&quot;&quot& so on using the & inbetween the IF functions allows you to have more then 7. They are independent IF functions.

But, I think the preferred way is to do the VLOOKUP on the table. That way, you can change inputs/outputs very easily.



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[bluedragon2]

opps, see all those options listed above mine, must of had an old refresh when I did mine ...



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[SkipVought]

LINSEY,

If this has a regular pattern,ie .4 between values then

=IF(S2>1.8,&quot;+&quot;,IF(S2<=-1.8,&quot;-&quot;,ROUNDUP(MOD(((S2+1.8)*10)/4,10),0)))

might work

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[KenWright]

You can also put the VLOOKUP in a single cell, eg with Zathras' data:-

=VLOOKUP(S2,{-99999,&quot;'-&quot;;-1.8,&quot;'1&quot;;-1.4,&quot;'2&quot;;-1,&quot;'3&quot;;-0.6,&quot;'4&quot;;-0.2,&quot;'5&quot;;0.2,&quot;'6&quot;;0.6,&quot;'7&quot;;1,&quot;'8&quot;;1.4,&quot;'9&quot;;1.8,&quot;'+&quot;},2)

but, note that with this set of data, it will give different results to the OPs example if the value in S2 is exactly one of the values in the table, as the OPs examples were all for values 'above or greater than' the values in his table, eg

With S2 = 0.2, OPs example would yield 5, whereas the VLOOKUP table as it stands will yield 6.

Regards
Ken................


----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------