How to find the address of the cell containing the max value in an Excel row, skipping every other cell in...
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
microsoft-excel
asked Jan 25 '13 at 16:17
Gary BarrettGary Barrett
2602512
add a comment |
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
microsoft-excel
asked Jan 25 '13 at 16:17
Gary BarrettGary Barrett
2602512
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
add a comment |
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
microsoft-excel
asked Jan 25 '13 at 16:17
Gary BarrettGary Barrett
2602512
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
microsoft-excel
microsoft-excel
asked Jan 25 '13 at 16:17
Gary BarrettGary Barrett
2602512
asked Jan 25 '13 at 16:17
Gary BarrettGary Barrett
2602512
edited Jan 25 '13 at 16:57
Gary Barrett
asked Jan 25 '13 at 16:17
Gary BarrettGary Barrett
2602512
asked Jan 25 '13 at 16:17
Gary BarrettGary Barrett
2602512
asked Jan 25 '13 at 16:17
Gary BarrettGary Barrett
2602512
2602512
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
add a comment |
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
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
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
....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
add a comment |
2 Answers
2
active
oldest
votes
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!
answered Jan 25 '13 at 17:44
Gary BarrettGary Barrett
2602512
add a comment |
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?
answered Jan 25 '13 at 19:50
barry houdinibarry houdini
10k11420
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f541683%2fhow-to-find-the-address-of-the-cell-containing-the-max-value-in-an-excel-row-sk%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
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!
answered Jan 25 '13 at 17:44
Gary BarrettGary Barrett
2602512
add a comment |
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!
answered Jan 25 '13 at 17:44
Gary BarrettGary Barrett
2602512
add a comment |
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!
answered Jan 25 '13 at 17:44
Gary BarrettGary Barrett
2602512
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!
answered Jan 25 '13 at 17:44
Gary BarrettGary Barrett
2602512
answered Jan 25 '13 at 17:44
Gary BarrettGary Barrett
2602512
answered Jan 25 '13 at 17:44
Gary BarrettGary Barrett
2602512
answered Jan 25 '13 at 17:44
Gary BarrettGary Barrett
2602512
2602512
add a comment |
add a comment |
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?
answered Jan 25 '13 at 19:50
barry houdinibarry houdini
10k11420
add a comment |
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?
answered Jan 25 '13 at 19:50
barry houdinibarry houdini
10k11420
add a comment |
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?
answered Jan 25 '13 at 19:50
barry houdinibarry houdini
10k11420
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?
answered Jan 25 '13 at 19:50
barry houdinibarry houdini
10k11420
answered Jan 25 '13 at 19:50
barry houdinibarry houdini
10k11420
answered Jan 25 '13 at 19:50
barry houdinibarry houdini
10k11420
answered Jan 25 '13 at 19:50
barry houdinibarry houdini
10k11420
10k11420
add a comment |
add a comment |
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f541683%2fhow-to-find-the-address-of-the-cell-containing-the-max-value-in-an-excel-row-sk%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
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