Using MIN with multiple MATCH criteria (Track and Field Stats)
As a track coach, I want to keep a running spread sheet of the athletes' best performances over various events in Excel. I'll then use conditional formatting to highlight athletes that qualify for post-season meets based on their running season bests.
The overall stats ("Season Stats Summary") sheet I have looks like this, with athlete names in column A, events in columns B through (whatever), and then the athlete's best (MIN) time they've achieved to date in the season (leaving cells blank if there's no mark recorded yet).
As we complete meets,
I can dump meet results into a separate raw data sheet such as this:
I'd like to use a formula in the overall Season Stats sheet that matches athletes to events and finds the best (minimum) time they've run for that event. I can take it from there with the conditional formatting, but I can't figure out how to retrieve the best score given the two matching criteria and allowing for duplicate name/event matches.
microsoft-excel
edited Dec 26 '18 at 0:08
Scott
15.6k113889
asked Dec 25 '18 at 23:38
BufordBuford
61
add a comment |
As a track coach, I want to keep a running spread sheet of the athletes' best performances over various events in Excel. I'll then use conditional formatting to highlight athletes that qualify for post-season meets based on their running season bests.
The overall stats ("Season Stats Summary") sheet I have looks like this, with athlete names in column A, events in columns B through (whatever), and then the athlete's best (MIN) time they've achieved to date in the season (leaving cells blank if there's no mark recorded yet).
As we complete meets,
I can dump meet results into a separate raw data sheet such as this:
I'd like to use a formula in the overall Season Stats sheet that matches athletes to events and finds the best (minimum) time they've run for that event. I can take it from there with the conditional formatting, but I can't figure out how to retrieve the best score given the two matching criteria and allowing for duplicate name/event matches.
microsoft-excel
edited Dec 26 '18 at 0:08
Scott
15.6k113889
asked Dec 25 '18 at 23:38
BufordBuford
61
add a comment |
As a track coach, I want to keep a running spread sheet of the athletes' best performances over various events in Excel. I'll then use conditional formatting to highlight athletes that qualify for post-season meets based on their running season bests.
The overall stats ("Season Stats Summary") sheet I have looks like this, with athlete names in column A, events in columns B through (whatever), and then the athlete's best (MIN) time they've achieved to date in the season (leaving cells blank if there's no mark recorded yet).
As we complete meets,
I can dump meet results into a separate raw data sheet such as this:
I'd like to use a formula in the overall Season Stats sheet that matches athletes to events and finds the best (minimum) time they've run for that event. I can take it from there with the conditional formatting, but I can't figure out how to retrieve the best score given the two matching criteria and allowing for duplicate name/event matches.
microsoft-excel
edited Dec 26 '18 at 0:08
Scott
15.6k113889
asked Dec 25 '18 at 23:38
BufordBuford
61
As a track coach, I want to keep a running spread sheet of the athletes' best performances over various events in Excel. I'll then use conditional formatting to highlight athletes that qualify for post-season meets based on their running season bests.
The overall stats ("Season Stats Summary") sheet I have looks like this, with athlete names in column A, events in columns B through (whatever), and then the athlete's best (MIN) time they've achieved to date in the season (leaving cells blank if there's no mark recorded yet).
As we complete meets,
I can dump meet results into a separate raw data sheet such as this:
I'd like to use a formula in the overall Season Stats sheet that matches athletes to events and finds the best (minimum) time they've run for that event. I can take it from there with the conditional formatting, but I can't figure out how to retrieve the best score given the two matching criteria and allowing for duplicate name/event matches.
microsoft-excel
microsoft-excel
edited Dec 26 '18 at 0:08
Scott
15.6k113889
asked Dec 25 '18 at 23:38
BufordBuford
61
edited Dec 26 '18 at 0:08
Scott
15.6k113889
asked Dec 25 '18 at 23:38
BufordBuford
61
edited Dec 26 '18 at 0:08
Scott
15.6k113889
edited Dec 26 '18 at 0:08
Scott
15.6k113889
edited Dec 26 '18 at 0:08
Scott
15.6k113889
15.6k113889
asked Dec 25 '18 at 23:38
BufordBuford
61
asked Dec 25 '18 at 23:38
BufordBuford
61
asked Dec 25 '18 at 23:38
BufordBuford
61
61
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
If you have Excel 2019 or Office 365, you can use the MINIFS function to get the minimum value that satisfies multiple criteria.
Assuming that the raw data in your question is in columns A:E of "Sheet1", and your Season Stats Summary is in columns A:H of another sheet, put the following formula in cell B2 of the "Season Stats Summary" sheet and copy it to the remaining cells.
=MINIFS(Sheet1!$E$1:$E$5,Sheet1!$D$1:$D$5,$A2,Sheet1!$C$1:$C$5,B$1)
This finds the lowest value in column E (mark) of the raw data where the athlete name (column D in raw data) equals this athlete's name and the event (column C in raw data) is the event listed in this column's header.
Note that the best time will be listed as zero for events that the athlete never competed in. You can set the number format of the cells to display zero as blank, or if you need the value of the cell to be blank, change the formula to:
=IF(MINIFS(Sheet1!$E$1:$E$5,Sheet1!$D$1:$D$5,$A2,Sheet1!$C$1:$C$5,B$1)=0,"",MINIFS(Sheet1!$E$1:$E$5,Sheet1!$D$1:$D$5,$A2,Sheet1!$C$1:$C$5,B$1))
answered Dec 26 '18 at 2:45
BlackwoodBlackwood
2,89461728
Thank you! This was exactly what I needed. (I up-voted but I'm new so it may not count).
– Buford
Dec 27 '18 at 0:31
add a comment |
How it Works:
- Sample Data Range is A17:G24, including Headers.
Write this Array Formula in Cell B28, finish with Ctrl+Shift+Enter, fill Right then Down.
{=MIN(IF($A$19:$A$24=$A28,C$19:C$24))}
Adjust cell references in the formula as needed.
answered Dec 26 '18 at 9:30
Rajesh SRajesh S
1
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%2f1387679%2fusing-min-with-multiple-match-criteria-track-and-field-stats%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
If you have Excel 2019 or Office 365, you can use the MINIFS function to get the minimum value that satisfies multiple criteria.
Assuming that the raw data in your question is in columns A:E of "Sheet1", and your Season Stats Summary is in columns A:H of another sheet, put the following formula in cell B2 of the "Season Stats Summary" sheet and copy it to the remaining cells.
=MINIFS(Sheet1!$E$1:$E$5,Sheet1!$D$1:$D$5,$A2,Sheet1!$C$1:$C$5,B$1)
This finds the lowest value in column E (mark) of the raw data where the athlete name (column D in raw data) equals this athlete's name and the event (column C in raw data) is the event listed in this column's header.
Note that the best time will be listed as zero for events that the athlete never competed in. You can set the number format of the cells to display zero as blank, or if you need the value of the cell to be blank, change the formula to:
=IF(MINIFS(Sheet1!$E$1:$E$5,Sheet1!$D$1:$D$5,$A2,Sheet1!$C$1:$C$5,B$1)=0,"",MINIFS(Sheet1!$E$1:$E$5,Sheet1!$D$1:$D$5,$A2,Sheet1!$C$1:$C$5,B$1))
answered Dec 26 '18 at 2:45
BlackwoodBlackwood
2,89461728
Thank you! This was exactly what I needed. (I up-voted but I'm new so it may not count).
– Buford
Dec 27 '18 at 0:31
add a comment |
If you have Excel 2019 or Office 365, you can use the MINIFS function to get the minimum value that satisfies multiple criteria.
Assuming that the raw data in your question is in columns A:E of "Sheet1", and your Season Stats Summary is in columns A:H of another sheet, put the following formula in cell B2 of the "Season Stats Summary" sheet and copy it to the remaining cells.
=MINIFS(Sheet1!$E$1:$E$5,Sheet1!$D$1:$D$5,$A2,Sheet1!$C$1:$C$5,B$1)
This finds the lowest value in column E (mark) of the raw data where the athlete name (column D in raw data) equals this athlete's name and the event (column C in raw data) is the event listed in this column's header.
Note that the best time will be listed as zero for events that the athlete never competed in. You can set the number format of the cells to display zero as blank, or if you need the value of the cell to be blank, change the formula to:
=IF(MINIFS(Sheet1!$E$1:$E$5,Sheet1!$D$1:$D$5,$A2,Sheet1!$C$1:$C$5,B$1)=0,"",MINIFS(Sheet1!$E$1:$E$5,Sheet1!$D$1:$D$5,$A2,Sheet1!$C$1:$C$5,B$1))
answered Dec 26 '18 at 2:45
BlackwoodBlackwood
2,89461728
Thank you! This was exactly what I needed. (I up-voted but I'm new so it may not count).
– Buford
Dec 27 '18 at 0:31
add a comment |
If you have Excel 2019 or Office 365, you can use the MINIFS function to get the minimum value that satisfies multiple criteria.
Assuming that the raw data in your question is in columns A:E of "Sheet1", and your Season Stats Summary is in columns A:H of another sheet, put the following formula in cell B2 of the "Season Stats Summary" sheet and copy it to the remaining cells.
=MINIFS(Sheet1!$E$1:$E$5,Sheet1!$D$1:$D$5,$A2,Sheet1!$C$1:$C$5,B$1)
This finds the lowest value in column E (mark) of the raw data where the athlete name (column D in raw data) equals this athlete's name and the event (column C in raw data) is the event listed in this column's header.
Note that the best time will be listed as zero for events that the athlete never competed in. You can set the number format of the cells to display zero as blank, or if you need the value of the cell to be blank, change the formula to:
=IF(MINIFS(Sheet1!$E$1:$E$5,Sheet1!$D$1:$D$5,$A2,Sheet1!$C$1:$C$5,B$1)=0,"",MINIFS(Sheet1!$E$1:$E$5,Sheet1!$D$1:$D$5,$A2,Sheet1!$C$1:$C$5,B$1))
answered Dec 26 '18 at 2:45
BlackwoodBlackwood
2,89461728
If you have Excel 2019 or Office 365, you can use the MINIFS function to get the minimum value that satisfies multiple criteria.
Assuming that the raw data in your question is in columns A:E of "Sheet1", and your Season Stats Summary is in columns A:H of another sheet, put the following formula in cell B2 of the "Season Stats Summary" sheet and copy it to the remaining cells.
=MINIFS(Sheet1!$E$1:$E$5,Sheet1!$D$1:$D$5,$A2,Sheet1!$C$1:$C$5,B$1)
This finds the lowest value in column E (mark) of the raw data where the athlete name (column D in raw data) equals this athlete's name and the event (column C in raw data) is the event listed in this column's header.
Note that the best time will be listed as zero for events that the athlete never competed in. You can set the number format of the cells to display zero as blank, or if you need the value of the cell to be blank, change the formula to:
=IF(MINIFS(Sheet1!$E$1:$E$5,Sheet1!$D$1:$D$5,$A2,Sheet1!$C$1:$C$5,B$1)=0,"",MINIFS(Sheet1!$E$1:$E$5,Sheet1!$D$1:$D$5,$A2,Sheet1!$C$1:$C$5,B$1))
answered Dec 26 '18 at 2:45
BlackwoodBlackwood
2,89461728
edited Dec 26 '18 at 2:50
answered Dec 26 '18 at 2:45
BlackwoodBlackwood
2,89461728
answered Dec 26 '18 at 2:45
BlackwoodBlackwood
2,89461728
answered Dec 26 '18 at 2:45
BlackwoodBlackwood
2,89461728
2,89461728
Thank you! This was exactly what I needed. (I up-voted but I'm new so it may not count).
– Buford
Dec 27 '18 at 0:31
add a comment |
Thank you! This was exactly what I needed. (I up-voted but I'm new so it may not count).
– Buford
Dec 27 '18 at 0:31
Thank you! This was exactly what I needed. (I up-voted but I'm new so it may not count).
– Buford
Dec 27 '18 at 0:31
Thank you! This was exactly what I needed. (I up-voted but I'm new so it may not count).
– Buford
Dec 27 '18 at 0:31
add a comment |
How it Works:
- Sample Data Range is A17:G24, including Headers.
Write this Array Formula in Cell B28, finish with Ctrl+Shift+Enter, fill Right then Down.
{=MIN(IF($A$19:$A$24=$A28,C$19:C$24))}
Adjust cell references in the formula as needed.
answered Dec 26 '18 at 9:30
Rajesh SRajesh S
1
add a comment |
How it Works:
- Sample Data Range is A17:G24, including Headers.
Write this Array Formula in Cell B28, finish with Ctrl+Shift+Enter, fill Right then Down.
{=MIN(IF($A$19:$A$24=$A28,C$19:C$24))}
Adjust cell references in the formula as needed.
answered Dec 26 '18 at 9:30
Rajesh SRajesh S
1
add a comment |
How it Works:
- Sample Data Range is A17:G24, including Headers.
Write this Array Formula in Cell B28, finish with Ctrl+Shift+Enter, fill Right then Down.
{=MIN(IF($A$19:$A$24=$A28,C$19:C$24))}
Adjust cell references in the formula as needed.
answered Dec 26 '18 at 9:30
Rajesh SRajesh S
1
How it Works:
- Sample Data Range is A17:G24, including Headers.
Write this Array Formula in Cell B28, finish with Ctrl+Shift+Enter, fill Right then Down.
{=MIN(IF($A$19:$A$24=$A28,C$19:C$24))}
Adjust cell references in the formula as needed.
answered Dec 26 '18 at 9:30
Rajesh SRajesh S
1
edited Dec 26 '18 at 9:40
answered Dec 26 '18 at 9:30
Rajesh SRajesh S
1
answered Dec 26 '18 at 9:30
Rajesh SRajesh S
1
answered Dec 26 '18 at 9:30
Rajesh SRajesh S
1
1
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%2f1387679%2fusing-min-with-multiple-match-criteria-track-and-field-stats%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
