[Pancho sanza]

Excel seems to only allow a maximum of 8 conditional IF statements in a cell, anyone know why and a way to get around this?

thanks

here is how my formula looks currently, wanted to add on more if statements.


Cell N20

=IF(E20=-18.5,0.0512,IF(E20=-18,0.0349,IF(E20=-17.5,0.0267,IF(E20=-17,0.0068,IF(E20=-16.5,0,IF(E20=12.5,0.4945,IF(E20=12,0.50 15,IF(E20=11.5,0.4586))))))))

[Wrecktangle]

Is this Excel 2003? I think 2007 allows more.

Rather than all the conditionals, why not use a lookup table (vlookup)? I have lookup tables that have thousands of values. However, for a lookup table to work properly the gate value must be arranged (sorted) from small to large in ASCII manner.

[Data]

Excel seems to only allow a maximum of 8 conditional IF statements in a cell, anyone know why and a way to get around this?

I do not know why is that like this but I deal with this by having a sheet separate from data where I keep variety of stuff including tables for VLOOKUPs. So, I would put your numbers in two-column table and then do vlookup of E20 against that table.

[Pokerjoe]

Data beat me to it.
Alternatively, write a formula translating values generically. Most points are worth about the same; "if" the important ones, calc the normal ones.
And yeah, you must be using an old excel version.

[TomG]

There should be a much better way to accomplish your goal than a super long conditional. If you must, though, you can reach the limit of 8 conditionals in one cell (A1 for example), and then begin anew by pointing to the value in A1 with another new set of conditionals. I hope you don't make any mistakes in the process because error checking this will be a nightmare.