This formula uses more levels of nesting than you can use in the current file format

[MR WILLIAMS]

Hello folks,

I am trying to use the below formula but it's too large, apparently. Can anyone tell me what I need to do to shorten it? Thank you so much



=IF(B8<6.99,B8*0,IF(B8<7.99,B8*100,IF(B8<8.99,B8*150,IF(B8<9.99,B8*200,IF(B8<10.99,B8*250,IF(B8<11.99,B8*300,IF(B8<12.99,B8*375,IF(B8<13.99,B8*500,IF(B8<14.99,B8*566,IF(B8<15.99,B8*576,IF(B8<16.99,B8*585,IF(B8<16.99,B8*585,IF(B8<17.99,B8*639,IF(B8<18.99,B8*647,IF(B8<19.99,B8*715,IF(B8<20.99,B8*722,IF(B8<21.99,B8*728,IF(B8<22.99,B8*733,IF(B8<23.99,B8*738,IF(B8<24.99,B8*743,IF(B8<25.99,B8*747,IF(B8<26.99,B8*751,IF(B8<27.99,B8*755,IF(B8<28.99,B*758,IF(B8<29.99,B*761,IF(B8<30.99,B8*764,IF(B8<31.99,B8*767,IF(B8<32.99,B8*769,IF(B8<33.99,B8*769,IF(B8<34.99,B8*774,IF(B8<35,B8*776))))))))))))))))))))))))))))))+2500

 

[Will85]

Can you create a table on a separate tab?

 

[ClaireS]

I'm not surprised Excel choked - you can only nest functions 7 deep, and you have 30 levels!

I would put your reference values into a list on another sheet and use VLOOKUP in your formula. If you use it WITHOUT the optional fourth argument, it will give you an approximate match. You will need to start with 0 then the first figure that actually gives you a new value, thus:

0 0
7 100
8 150
etc

As long as you keep the lookup column (0,7,8 etc) sorted into ascending order, a VLOOKUP will return the previous value if it doesn't find an exact match, so anything up to 6.99 will return 0, 7 to 7.99 will return 100 and so on.