HELP!! specified formula cannot be entered because it uses more than 64 nesting :(

[krisjohn2000000]

this formula is working:

=IF(AND(C15<=95,C15>=94.8),"0%",IF(AND(C15<=123.5,C15>=123.3),"0%",IF(OR(C15=94.7,C15=94.6),"5%",IF(OR(C15=123.2,C15=123.1),"4%",IF(AND(C15<=94.5,C15>=94.3),"10%",IF(AND(C15<=123,C15>=122.8),"8%",IF(OR(C15=94.2,C15=94.1),"15%",IF(OR(C15=122.7,C15=122.6),"12%",IF(AND(C15<=94,C15>=93.8),"20%",IF(AND(C15<=122.5,C15>=122.3),"16%",IF(OR(C15=93.7,C15=93.6),"25%",IF(OR(C15=122.2,C15=122.1),"20%",IF(AND(C15<=93.5,C15>=93.3),"30%",IF(AND(C15<=122,C15>=121.8),"24%",IF(OR(C15=93.2,C15=93.1),"35%",IF(OR(C15=121.6,C15=121.7),"28%",IF(AND(C15<=93,C15>=92.8),"40%",IF(AND(C15<=121.5,C15>=121.3),"32%",IF(OR(C15=92.7,C15=92.6),"45%",IF(OR(C15=121.2,C15>=121.1),"36%",IF(AND(C15<=92.5,C15>=92.3),"50%",IF(AND(C15<=121,C15>=120.8),"40%",IF(OR(C15=92.2,C15=92.1),"55%",IF(OR(C15=120.7,C15>=120.6),"44%",IF(AND(C15<=92,C15>=91.8),"60%",IF(AND(C15<=120.5,C15>=120.3),"46%",IF(AND(C15<=91.7,C15>=91.5,),"65%",IF(AND(C15<=120.2,C15>=120),"48%",IF(OR(C15=91.4,C15=91.3),"70%",IF(AND(C15<=119.9,C15>=119.8),"50%",IF(AND(C15<=91.2,C15>=90),"72%",IF(AND(C15<=119.7,C15>=119.5),"52%",IF(OR(C15=90.9,C15=90.8),"74%",IF(OR(C15=119.4,C15=119.3),"54%",IF(AND(C15<=90.7,C15>=90.5),"76%",IF(AND(C15<=119.2,C15>=119),"56%",IF(OR(C15=90.4,C15=90.3),"78%",IF(OR(C15=118.9,C15=118.8),"58%",IF(AND(C15<=90.2,C15>=90),"80%",IF(AND(C15<=118.7,C15>=118.5),"60%",IF(OR(C15=89.9,C15=89.8),"82%",IF(OR(C15=118.4,C15=118.3),"62%",IF(AND(C15<=89.7,C15>=89.5),"84%",IF(AND(C15<=118.2,C15>=118),"64%",IF(OR(C15=89.4,C15=89.3),"86%",IF(OR(C15=117.9,C15>=117.8),"66%",IF(AND(C15<=89.2,C15>=89),"88%",IF(AND(C15<=117.7,C15>=117.5),"68%",IF(OR(C15=88.9,C15=88.8),"90%",IF(OR(C15=117.4,C15>=117.3),"70%",IF(AND(C15<=88.7,C15>=88.5),"92%",IF(AND(C15<=117.2,C15>=117),"72%",IF(AND(C15<=88.4,C15>=88.2),"94%",IF(AND(C15<=116.9,C15>=116.7),"74%",IF(OR(C15=88.1,C15=88),"96%",IF(OR(C15=116.6,C15=116.5),"76%",IF(AND(C15<=87.9,C15>=87.7),"98%",IF(AND(C15<=116.4,C15>=116.2),"78%",IF(OR(C15=87.6,C15=87.5),"100%",IF(OR(C15=116.1,C15=116),"60%",IF(AND(C15<=87.4,C15>=87.2),"102%",IF(AND(C15<=115.9,C15>=115.7),"82%",IF(OR(C15=87.1,C15=87),"104%",IF(OR(C15=115.6,C15=115.5),"84%"," "))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))


but i need to add more if conditions. what should i do?

 

[pleeseemailme]

It looks like you have so many hard coded values that a lookup table would work well for you.

Alternatively, you could add another column that feeds off the preceding formula. It looks like all your nested IFs follow the format if a=b then 'done', else, check again and repeat. So you could set up another column that starts by saying if previous (above formula) is false then continue on in your 'check again and repeat' loop.

 

[krisjohn2000000]

how can i use that look up table? sorry im new in ms excel.

 

[PaddyD]

Also, if you could define the logic behind the calculations, might be possible to take a shorter approach

 

[krisjohn2000000]

Also, if you could define the logic behind the calculations, might be possible to take a shorter approach

it very difficult to find the logic of formula. the table is like this:

a b
95 0
94.7 5
94.5 10
94.2 15
94 20
93.7 25
93.5 30
.
.
.
.
.
.

 

[mikerickson]

Try
=5*ROUND(4*(95-A1),0)

 

[pleeseemailme]

it very difficult to find the logic of formula. the table is like this:

a b
95 0
94.7 5
94.5 10
94.2 15
94 20
93.7 25
93.5 30
.
.
.
.
.
.

A table like that could still be managed. But your formula is referring to a cell in column C and the example above is for columns A and B. Where is the value in C15 coming from? And it looks like your output data is all percentages. Is there a table where those are?

 

[krisjohn2000000]

thank you very much sir mike.

 

[krisjohn2000000]

there are two columns only on this table. i might gave you the wrong idea when i did that chart. column b is on percentage.

 

[pleeseemailme]

Has your question been answered or are you still needing a way to simplify the original formula with a table?