Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

On each row, I would like to get the address of the cell with the largest value, skipping every other cell e.g. I want to only assess the values in columns A,C,E,H,J and ignore the values in columns B,D,F,I,K

I think I need something similar to this:

=CELL("address",INDEX(1:1,MATCH(MAX(1:1),1:1,0)))

but skipping every other column however I can't get it to work

EDIT: What I want after identifying the max cell is to then a) ideally: display the cell's column header text (row 1) or b) at least display the column letter

share|improve this question
    
Do you want the address in order to do something else or is that the end result? - if it's the former then getting an address is not normally the best way to go.... –  barry houdini Jan 25 '13 at 16:31
    
....also....Do you have any header values in the alternate columns with common text or similar? If you do that's the simplest way to refer to the columns you need –  barry houdini Jan 25 '13 at 16:38

2 Answers 2

Progress! I can get MAX to skip every other cell like this:

=MAX(A2,C2,E2)

And to then display the max cell's heading text, this works:

=INDEX($A$1:$F$1,1,MATCH(MAX(B2,D2,F2),A2:F2,0))

This is not perfect though as although MAX skips every other cell as desired, the outer MATCH and INDEX functions do not, so if an odd column has a duplicate of the MAX value, it could still display the wrong heading. But its close enough for now!

share|improve this answer

This version would ensure that you only ever get the value from B1, D1 or F1, whatever the values in A2, C2 or E2

=INDEX(CHOOSE({1,2,3},B$1,D$1,F$1),MATCH(MAX(B2,D2,F2),CHOOSE({1,2,3},B2,D2,F2),0))

Of course that would be a long formula if extended over a much larger range - what's the real range that you need to look at?

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.