Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Constructing a general indicator function (without using nes

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I would like to construct an indicator function, however, preferably not with millions of nested if functions. I need it for situations like this:

    YV VA OE TM Indicator
    9 YV
    4 YV
    5 YV
    6 YV
    7 YV
    6 YV
    7 YV
    23 VA
    3 VA
    5 VA
    5 VA
    3 YV
    6 OE
    6 OE
    3 OE
    2 VA
    2 TM
    1 YV
    2 TM
    2 TM
    1 TM



    A nested IF function works here for me to create the indicator. However, if I had 40 fields instead of 4 then I couldn't use this method. I am guessing that using a correct lookup function would do it but I am not sure how to go about it. Thanks.

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    YV VA OE TM Indicator
    3 0 0 0 YV
    4 0 0 0 YV
    5 0 0 0 YV
    6 0 0 0 YV
    7 0 0 0 YV
    6 0 0 0 YV
    7 0 0 0 YV
    0 23 0 0 VA
    0 3 0 0 VA
    0 5 0 0 VA
    0 5 0 0 VA
    3 0 0 0 YV
    0 0 6 0 OE
    0 0 6 0 OE
    0 0 3 0 OE
    0 2 0 0 VA
    0 0 0 2 TM
    1 0 0 0 YV
    0 0 0 2 TM
    0 0 0 2 TM
    0 0 0 1 TM

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry about this, they are supposed to be 5 columns with YV, VA, OE, TM, and Indicator being the column headings.

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    With your data entered into cells A1:E22 enter the array formula...

    {=INDEX($A$1:$D$1,1,MAX(($A2:$D2<>0)*COLUMN($A:$D)))}

    ...into cell E2 and copy down to cell E22.

    Note: Array formulas must be entered using the Shift+Control+Enter key combination. For more on array formulas consult the Excel Help topic for "About array formulas and how to enter them".

    [ This Message was edited by: Mark W. on 2002-03-21 09:17 ]

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,653
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-03-21 09:07, RET79 wrote:
    YV VA OE TM Indicator
    3 0 0 0 YV
    4 0 0 0 YV
    5 0 0 0 YV
    6 0 0 0 YV
    7 0 0 0 YV
    6 0 0 0 YV
    7 0 0 0 YV
    0 23 0 0 VA
    0 3 0 0 VA
    0 5 0 0 VA
    0 5 0 0 VA
    3 0 0 0 YV
    0 0 6 0 OE
    0 0 6 0 OE
    0 0 3 0 OE
    0 2 0 0 VA
    0 0 0 2 TM
    1 0 0 0 YV
    0 0 0 2 TM
    0 0 0 2 TM
    0 0 0 1 TM
    Supposing that the above data start in A1,

    in E2 enter and drag down:

    =IF(MAX(A2:D2),INDEX($A$1:$D$1,MATCH(MAX(A2:D2),A2:D2,0)),"")

    Is this what you are asking for?

    Aladin

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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