IF FUNCTION Limitations question

[rugby199993]

Help would be very much appreciated. I will try my best to explain wha
I am trying to do so let me know if you do not understand and I will tr
again explaining it.

First, I get a value for about 100 individuals that ranges within th
table below. Once I figure out the value, I set up an IF statemen
that gets the range for each individual. (E
IF(AND(+$AF6<=-0.5,$AF6>-1),AJ$4,0). By doing this I can get eac
persons range. Here is where I am having the problem. I have
columns of data but can't use the IF function b/c it is limited to
formulas. Example of what I was doing after the first IF statemen
(=IF(AJ6=1,AJ$3,IF(AK6=1,AK$3,IF(AL6=1,AL$3,IF(AM6=1,AM$3,IF(AN6=1,AN$3,IF(AO6=1,AO$3,IF(AP6=1,AP$3,IF(AQ6=1,AQ$3,)))))))
In this formula, I was saying IF one of the 7 columns is equal to
than select the Range that is associated with it. It would work i
there were only 7 ranges but I have 9.

Range Values
Range 4 >=2
Range 3 >=1 .5 & <2
Range 2 >=1 & <1.5
Range 1 >=.5 &<1
Range 0 >-.5 & <.5
Range -1 <=-.5 &>-1
Range -2 <=-1 &>-1.5
Range -3 <=-1.5 &>-2
Range -4 <=-2

Please let me know if I explained and if anyone has a clue how to solv
this it would be greatly appreciated.
Thanks,

Pau

 

[Scottmk]

I found the following link useful once when I ran into the same sort o
problem: http://www.cpearson.com/excel/nested.htm
It worked for me

 

[Frank Kabel]

Hi
the limit is 7 nested function. in your case you may try the following
formula
=INDEX(AJ3:AQ3,MATCH(1,AJ6:AQ6,0))

 

[Ron Rosenfeld]

Help would be very much appreciated. I will try my best to explain what
I am trying to do so let me know if you do not understand and I will try
again explaining it.

First, I get a value for about 100 individuals that ranges within the
table below. Once I figure out the value, I set up an IF statement
that gets the range for each individual. (EG
IF(AND(+$AF6<=-0.5,$AF6>-1),AJ$4,0). By doing this I can get each
persons range. Here is where I am having the problem. I have 8
columns of data but can't use the IF function b/c it is limited to 7
formulas. Example of what I was doing after the first IF statement
(=IF(AJ6=1,AJ$3,IF(AK6=1,AK$3,IF(AL6=1,AL$3,IF(AM6=1,AM$3,IF(AN6=1,AN$3,IF(AO6=1,AO$3,IF(AP6=1,AP$3,IF(AQ6=1,AQ$3,))))))))
In this formula, I was saying IF one of the 7 columns is equal to 1
than select the Range that is associated with it. It would work if
there were only 7 ranges but I have 9.

Range Values
Range 4 >=2
Range 3 >=1 .5 & <2
Range 2 >=1 & <1.5
Range 1 >=.5 &<1
Range 0 >-.5 & <.5
Range -1 <=-.5 &>-1
Range -2 <=-1 &>-1.5
Range -3 <=-1.5 &>-2
Range -4 <=-2

Please let me know if I explained and if anyone has a clue how to solve
this it would be greatly appreciated.
Thanks,

Paul

Usually, when you run out of IF's, either a mathematical solution or one of the
LOOKUP functions (HLOOKUP, VLOOKUP, LOOKUP, INDEX & MATCH) will be effective.

Here's a mathematical solution with your Value in A1:

=MIN(4,MAX(-4,FLOOR(A1,0.5*SIGN(A1))*2))


--ron