Nested Functions Limits

[scantor]

I have the following formula in a cell:

Please Login or Register  to view this content.

I actually wanted to have one more IF(AND(... condition whre you see the "5" at the end. Unfortunately I read that you can have up to 7 nested functions. Is there a way around this?

What I'm actually trying to do is check to see if a particular value is between two adjacent numbers, as in the colunmn below. For example if the value I'm checking in cell M20 is 0.35, then print a "1", if the value is 0.27 then print a "2". The reason I have so many IF statements is to cover the fact that the column of numbers may be in ascending order or descending order.

0.3995 in B7
0.3356 in B8
0.2573 in B9
0.1531 in B10
0.0698 in B11
0.0243 in B12

[martindwilson]

one way is to replace 5 with a cell reference and stick the final "if" in that cell

[NBVC]

if you add your resulting values in adjacent cells..
ie.

0.3995 1
0.3356 2
0.2573 3
0.1531 4
0.0698 5
0.0243 6

then you can use formula:

=INDEX($C$7:$C$12,MATCH(M20,$B$7:$B$12,-1))

where C7 to C12 contain the result values you want.