Thanks:  0
Likes:  0

# Thread: Constructing a general indicator function (without using nes

1. 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. 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. Sorry about this, they are supposed to be 5 columns with YV, VA, OE, TM, and Indicator being the column headings.

4. 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. 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?

## User Tag List

#### Posting Permissions

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