+ Reply to Thread
Results 1 to 4 of 4

Nested IF statement comparing value1 against multiple cells?

  1. #1
    Registered User
    Join Date
    11-05-2004
    Location
    Palm Springs California
    MS-Off Ver
    Office 2011 for Mac
    Posts
    37

    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. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,040

    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?
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,040

    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. #4
    Registered User
    Join Date
    11-05-2004
    Location
    Palm Springs California
    MS-Off Ver
    Office 2011 for Mac
    Posts
    37

    Re: Nested IF statement comparing value1 against multiple cells?

    Quote Originally Posted by Special-K View Post
    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).

    Thanks again for your help,

    Michael

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1