Help in long formula!

[teachertwish]

I'm trying to use the formula below, but it seems that it is too long for
Excel 97-2004. The formula works for Mac 2008 or Vista versions of Office,
but not in lower versions. My colleague only has a lower version and she
needs to use the file I need to send her. The formula only works when I
delete conditions from IF(D25>14....)

=IF(D25>29,100,IF(D25>27,99,IF(D25>25,98,IF(D25>23,97,IF(D25>21,96,IF(D25>19,95,IF(D25>17,94,IF(D25>14,93,IF(D25>12,
92,IF(D25>10,91,IF(D25>8,90,IF(D25>6,89,IF(D25>4,88,IF(D25>2,87,IF(D25>0,86,IF(D25=0,85))))))))))))))))

 

[Vitordf]

Hi,
Excel do not accept more then 7 IF nested formulas, however there is a work
around it, try something like this;
=IF(A16=1,1,"")&(IF(A16=2,2,"")&(IF(A16=3,3,"")&IF(A16=4,4,"")&(IF(A16=5,5,"")&IF(A16=6,6,"")&(IF(A16=7,7,"")&IF(A16=8,8,"")&IF(A16=9,9,"")))))

Regs,

 

[ShaneDevenshire]

Hi,

This is a problem for VLOOKUP:

Enter a little table such as the following in a range

0 85
0.0001 86
2.0001 87
4.0001 88
6.0001 89
8.0001 90
10.0001 91
12.0001 92
14.0001 93
17.0001 94
19.0001 95
21.0001 96
23.0001 97
25.0001 98
27.0001 99
29.0001 100

I entered this in A1:B16.

Then your formula becomes:

=VLOOKUP(D25,A1:B16,2)

The entries in column A have as many decimal places as you need based on the
number of decimal places in the numbers in D25. So if D25 can only have
whole numbers, no decimals, you could enter numbers like 0.1, 2.1, 4.1 and so
on.

Excel 2007 will allow you to do 64 levels of nesting but this problem should
be solved with VLOOKUP even in that version!