# Nested IF statement comparing value1 against multiple cells?

1. ## Nested IF statement comparing value1 against multiple cells?

Have the following array formula: =IF(L34=HOLEC,10,IF(L34=HOLEC-1,5,IF(L34=HOLEC+1,5,IF(L34=HOLEC-2,2,IF(L34=HOLEC+2,2,IF(L34=HOLEC-3,2,IF(L34=HOLEC+3,2,0)))))))

The values in L34 and in both cells of HOLEC are simple numerical values between 1 and 16.
HOLEC represents two adjacent cells. This formula works correctly but only for the values in the first cell of HOLEC not the second.
Can someone suggest what my problem may be or a work around?

Thanks, Michael

2. ## Re: Nested IF statement comparing value1 against multiple cells?

HOLEC is a named range
HOLEC-1 is not 1 cell back from HOLEC it is the value of HOLEC subtract 1
Is this where the confusion lies?

3. ## Re: Nested IF statement comparing value1 against multiple cells?

Try this instead of that long IF formula
(untested)
=LOOKUP(ABS(L34-HOLEC),{0,1,2,3,4},{10,5,2,2,0})

Though this wont solve your HOLEC problem

4. ## Re: Nested IF statement comparing value1 against multiple cells?

Originally Posted by Special-K
HOLEC is a named range
HOLEC-1 is not 1 cell back from HOLEC it is the value of HOLEC subtract 1
Is this where the confusion lies?
Hello Special-K:

Yes that is exactly what I intended. I want to determine if the value in cell L34 is = to or greater than either of the two cells identified by the range name "HOLEC" by 1, 2 or 3, or less than L34 by 1, 2 or 3. The assigned cell would then be given the following values: (= +10), (+/- 1 =+5), (+/- 2 +2), (+/-3 +2).

Michael

There are currently 1 users browsing this thread. (0 members and 1 guests)