Getting Minimum value in a range with multiple condition
I have a excel sheet
A. B. C.
X. I. 10/10/2018 06:27:54
X. I. 12/10/2018 13:00:00
X. U. 12/10/2018 13:01:20
Y. I. 13/10/2018 13:05:40
Y. U 15/10/2018 07:22:23
Y. U. 17/10/2018 08:20:43
Column A is customer, Column B is activity, C is Start time. How can get max value(column C) activity I for for customer X
excel datetime filter excel-formula spreadsheet
edited Nov 21 '18 at 12:58
Gary's Student
74.1k94064
asked Nov 21 '18 at 12:56
Shivam ShrivastavaShivam Shrivastava
42
add a comment |
I have a excel sheet
A. B. C.
X. I. 10/10/2018 06:27:54
X. I. 12/10/2018 13:00:00
X. U. 12/10/2018 13:01:20
Y. I. 13/10/2018 13:05:40
Y. U 15/10/2018 07:22:23
Y. U. 17/10/2018 08:20:43
Column A is customer, Column B is activity, C is Start time. How can get max value(column C) activity I for for customer X
excel datetime filter excel-formula spreadsheet
edited Nov 21 '18 at 12:58
Gary's Student
74.1k94064
asked Nov 21 '18 at 12:56
Shivam ShrivastavaShivam Shrivastava
42
What have you tried please? There are many examples of Max Ifs on SO. You may also be able to use a pivottable. Your title says minimum and question body says max - though same principle will apply.
– QHarr
Nov 21 '18 at 13:05
use the following formula to determine if your date time values in C are actually numbers or strings representing a date.=isnumber(C2)a result of true means its a number which means it will not need to be converted.
– Forward Ed
Nov 21 '18 at 14:52
add a comment |
I have a excel sheet
A. B. C.
X. I. 10/10/2018 06:27:54
X. I. 12/10/2018 13:00:00
X. U. 12/10/2018 13:01:20
Y. I. 13/10/2018 13:05:40
Y. U 15/10/2018 07:22:23
Y. U. 17/10/2018 08:20:43
Column A is customer, Column B is activity, C is Start time. How can get max value(column C) activity I for for customer X
excel datetime filter excel-formula spreadsheet
edited Nov 21 '18 at 12:58
Gary's Student
74.1k94064
asked Nov 21 '18 at 12:56
Shivam ShrivastavaShivam Shrivastava
42
I have a excel sheet
A. B. C.
X. I. 10/10/2018 06:27:54
X. I. 12/10/2018 13:00:00
X. U. 12/10/2018 13:01:20
Y. I. 13/10/2018 13:05:40
Y. U 15/10/2018 07:22:23
Y. U. 17/10/2018 08:20:43
Column A is customer, Column B is activity, C is Start time. How can get max value(column C) activity I for for customer X
excel datetime filter excel-formula spreadsheet
excel datetime filter excel-formula spreadsheet
edited Nov 21 '18 at 12:58
Gary's Student
74.1k94064
asked Nov 21 '18 at 12:56
Shivam ShrivastavaShivam Shrivastava
42
edited Nov 21 '18 at 12:58
Gary's Student
74.1k94064
asked Nov 21 '18 at 12:56
Shivam ShrivastavaShivam Shrivastava
42
edited Nov 21 '18 at 12:58
Gary's Student
74.1k94064
edited Nov 21 '18 at 12:58
Gary's Student
74.1k94064
edited Nov 21 '18 at 12:58
Gary's Student
74.1k94064
74.1k94064
asked Nov 21 '18 at 12:56
Shivam ShrivastavaShivam Shrivastava
42
asked Nov 21 '18 at 12:56
Shivam ShrivastavaShivam Shrivastava
42
asked Nov 21 '18 at 12:56
Shivam ShrivastavaShivam Shrivastava
42
42
What have you tried please? There are many examples of Max Ifs on SO. You may also be able to use a pivottable. Your title says minimum and question body says max - though same principle will apply.
– QHarr
Nov 21 '18 at 13:05
use the following formula to determine if your date time values in C are actually numbers or strings representing a date.=isnumber(C2)a result of true means its a number which means it will not need to be converted.
– Forward Ed
Nov 21 '18 at 14:52
add a comment |
What have you tried please? There are many examples of Max Ifs on SO. You may also be able to use a pivottable. Your title says minimum and question body says max - though same principle will apply.
– QHarr
Nov 21 '18 at 13:05
use the following formula to determine if your date time values in C are actually numbers or strings representing a date.=isnumber(C2)a result of true means its a number which means it will not need to be converted.
– Forward Ed
Nov 21 '18 at 14:52
What have you tried please? There are many examples of Max Ifs on SO. You may also be able to use a pivottable. Your title says minimum and question body says max - though same principle will apply.
– QHarr
Nov 21 '18 at 13:05
What have you tried please? There are many examples of Max Ifs on SO. You may also be able to use a pivottable. Your title says minimum and question body says max - though same principle will apply.
– QHarr
Nov 21 '18 at 13:05
use the following formula to determine if your date time values in C are actually numbers or strings representing a date.
=isnumber(C2) a result of true means its a number which means it will not need to be converted.– Forward Ed
Nov 21 '18 at 14:52
use the following formula to determine if your date time values in C are actually numbers or strings representing a date.
=isnumber(C2) a result of true means its a number which means it will not need to be converted.– Forward Ed
Nov 21 '18 at 14:52
add a comment |
3 Answers
3
active
oldest
votes
using a standard formula that performs array like calculations
AGGREGATE
Use the following formula. Since it performs array like calculations, avoid using full column references like F:F as it can lead to a lot of unnecessary calculations.
=AGGREGATE(14,6,C1:C6/((A1:A6="X")*(B1:B6="I")),1)
Instead of hard coding your search values, you can set up the formula to search based on values in other cells. I used the following formula in the example below:
=AGGREGATE(14,6,C1:C6/((A1:A6=$E2)*(B1:B6=$F2)),1)
This answer assumes that your date time in column C is stored as number. If it is stored as a string it will need a modification to the formula in order to convert it to a numerical date for excel.
answered Nov 21 '18 at 14:45
Forward EdForward Ed
6,79311339
Thank You. It worked...
– Shivam Shrivastava
Nov 22 '18 at 8:34
@ShivamShrivastava I would just add two comments on theAGGREGATEfunction : (1) hard to audit/understand, as the actual functionality is hidden behind codes/parameters (who knows without consulting the documentation that14meansLARGEand6meansignore errors??) (2) that second parameter can either be useful, if you know there can be errors in the input data and you purposely want to ignore these, or dangerous, in particular for larger input data ranges, as it will give you no feedback on errors in the input data, so results might be unexpected/wrong without you knowing.
– Peter K.
Nov 23 '18 at 12:55
add a comment |
No need for complex AGGREGATEs or nasty array formulas.
A nice, simple MAXIFS will do what you want:
=MAXIFS($C$2:$C$7,$A$2:$A$7,$E$2,$B$2:$B$7,$F$2)
answered Nov 21 '18 at 17:20
GravitateGravitate
1,4171326
Thanks for your reply. But MAXIFS function is not available in my excel version.
– Shivam Shrivastava
Nov 22 '18 at 8:35
add a comment |
I would do this with an array formula (ctrl+shift+enter) :=MAX((--(A1:A6="X."))*(--(B1:B6="I."))*(C1:C6))
Explanation:
- the
(A1:A6="X.")results in an array withTRUE/FALSEvalues where the condition is met; - the
--converts this in1/0 - the first
*(array multiplication) gives an array with1/0where both conditions are met; - the second
*gives an array with all values from column C that corresponds with the rows where both conditions are met; - the
MAXresults then in the maximum.
Just a tricky part with the dot behind the X and I. Not sure whether this is part of your data, if not you of course have to modify the condition. And instead of hard coding it in the formula, much better to work with cell reference.
answered Nov 21 '18 at 13:12
Peter K.Peter K.
763212
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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%2fstackoverflow.com%2fquestions%2f53412523%2fgetting-minimum-value-in-a-range-with-multiple-condition%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
using a standard formula that performs array like calculations
AGGREGATE
Use the following formula. Since it performs array like calculations, avoid using full column references like F:F as it can lead to a lot of unnecessary calculations.
=AGGREGATE(14,6,C1:C6/((A1:A6="X")*(B1:B6="I")),1)
Instead of hard coding your search values, you can set up the formula to search based on values in other cells. I used the following formula in the example below:
=AGGREGATE(14,6,C1:C6/((A1:A6=$E2)*(B1:B6=$F2)),1)
This answer assumes that your date time in column C is stored as number. If it is stored as a string it will need a modification to the formula in order to convert it to a numerical date for excel.
answered Nov 21 '18 at 14:45
Forward EdForward Ed
6,79311339
Thank You. It worked...
– Shivam Shrivastava
Nov 22 '18 at 8:34
@ShivamShrivastava I would just add two comments on theAGGREGATEfunction : (1) hard to audit/understand, as the actual functionality is hidden behind codes/parameters (who knows without consulting the documentation that14meansLARGEand6meansignore errors??) (2) that second parameter can either be useful, if you know there can be errors in the input data and you purposely want to ignore these, or dangerous, in particular for larger input data ranges, as it will give you no feedback on errors in the input data, so results might be unexpected/wrong without you knowing.
– Peter K.
Nov 23 '18 at 12:55
add a comment |
using a standard formula that performs array like calculations
AGGREGATE
Use the following formula. Since it performs array like calculations, avoid using full column references like F:F as it can lead to a lot of unnecessary calculations.
=AGGREGATE(14,6,C1:C6/((A1:A6="X")*(B1:B6="I")),1)
Instead of hard coding your search values, you can set up the formula to search based on values in other cells. I used the following formula in the example below:
=AGGREGATE(14,6,C1:C6/((A1:A6=$E2)*(B1:B6=$F2)),1)
This answer assumes that your date time in column C is stored as number. If it is stored as a string it will need a modification to the formula in order to convert it to a numerical date for excel.
answered Nov 21 '18 at 14:45
Forward EdForward Ed
6,79311339
Thank You. It worked...
– Shivam Shrivastava
Nov 22 '18 at 8:34
@ShivamShrivastava I would just add two comments on theAGGREGATEfunction : (1) hard to audit/understand, as the actual functionality is hidden behind codes/parameters (who knows without consulting the documentation that14meansLARGEand6meansignore errors??) (2) that second parameter can either be useful, if you know there can be errors in the input data and you purposely want to ignore these, or dangerous, in particular for larger input data ranges, as it will give you no feedback on errors in the input data, so results might be unexpected/wrong without you knowing.
– Peter K.
Nov 23 '18 at 12:55
add a comment |
using a standard formula that performs array like calculations
AGGREGATE
Use the following formula. Since it performs array like calculations, avoid using full column references like F:F as it can lead to a lot of unnecessary calculations.
=AGGREGATE(14,6,C1:C6/((A1:A6="X")*(B1:B6="I")),1)
Instead of hard coding your search values, you can set up the formula to search based on values in other cells. I used the following formula in the example below:
=AGGREGATE(14,6,C1:C6/((A1:A6=$E2)*(B1:B6=$F2)),1)
This answer assumes that your date time in column C is stored as number. If it is stored as a string it will need a modification to the formula in order to convert it to a numerical date for excel.
answered Nov 21 '18 at 14:45
Forward EdForward Ed
6,79311339
using a standard formula that performs array like calculations
AGGREGATE
Use the following formula. Since it performs array like calculations, avoid using full column references like F:F as it can lead to a lot of unnecessary calculations.
=AGGREGATE(14,6,C1:C6/((A1:A6="X")*(B1:B6="I")),1)
Instead of hard coding your search values, you can set up the formula to search based on values in other cells. I used the following formula in the example below:
=AGGREGATE(14,6,C1:C6/((A1:A6=$E2)*(B1:B6=$F2)),1)
This answer assumes that your date time in column C is stored as number. If it is stored as a string it will need a modification to the formula in order to convert it to a numerical date for excel.
answered Nov 21 '18 at 14:45
Forward EdForward Ed
6,79311339
edited Nov 21 '18 at 14:57
answered Nov 21 '18 at 14:45
Forward EdForward Ed
6,79311339
answered Nov 21 '18 at 14:45
Forward EdForward Ed
6,79311339
answered Nov 21 '18 at 14:45
Forward EdForward Ed
6,79311339
6,79311339
Thank You. It worked...
– Shivam Shrivastava
Nov 22 '18 at 8:34
@ShivamShrivastava I would just add two comments on theAGGREGATEfunction : (1) hard to audit/understand, as the actual functionality is hidden behind codes/parameters (who knows without consulting the documentation that14meansLARGEand6meansignore errors??) (2) that second parameter can either be useful, if you know there can be errors in the input data and you purposely want to ignore these, or dangerous, in particular for larger input data ranges, as it will give you no feedback on errors in the input data, so results might be unexpected/wrong without you knowing.
– Peter K.
Nov 23 '18 at 12:55
add a comment |
Thank You. It worked...
– Shivam Shrivastava
Nov 22 '18 at 8:34
@ShivamShrivastava I would just add two comments on theAGGREGATEfunction : (1) hard to audit/understand, as the actual functionality is hidden behind codes/parameters (who knows without consulting the documentation that14meansLARGEand6meansignore errors??) (2) that second parameter can either be useful, if you know there can be errors in the input data and you purposely want to ignore these, or dangerous, in particular for larger input data ranges, as it will give you no feedback on errors in the input data, so results might be unexpected/wrong without you knowing.
– Peter K.
Nov 23 '18 at 12:55
Thank You. It worked...
– Shivam Shrivastava
Nov 22 '18 at 8:34
Thank You. It worked...
– Shivam Shrivastava
Nov 22 '18 at 8:34
@ShivamShrivastava I would just add two comments on the
AGGREGATE function : (1) hard to audit/understand, as the actual functionality is hidden behind codes/parameters (who knows without consulting the documentation that 14 means LARGE and 6 means ignore errors??) (2) that second parameter can either be useful, if you know there can be errors in the input data and you purposely want to ignore these, or dangerous, in particular for larger input data ranges, as it will give you no feedback on errors in the input data, so results might be unexpected/wrong without you knowing.– Peter K.
Nov 23 '18 at 12:55
@ShivamShrivastava I would just add two comments on the
AGGREGATE function : (1) hard to audit/understand, as the actual functionality is hidden behind codes/parameters (who knows without consulting the documentation that 14 means LARGE and 6 means ignore errors??) (2) that second parameter can either be useful, if you know there can be errors in the input data and you purposely want to ignore these, or dangerous, in particular for larger input data ranges, as it will give you no feedback on errors in the input data, so results might be unexpected/wrong without you knowing.– Peter K.
Nov 23 '18 at 12:55
add a comment |
No need for complex AGGREGATEs or nasty array formulas.
A nice, simple MAXIFS will do what you want:
=MAXIFS($C$2:$C$7,$A$2:$A$7,$E$2,$B$2:$B$7,$F$2)
answered Nov 21 '18 at 17:20
GravitateGravitate
1,4171326
Thanks for your reply. But MAXIFS function is not available in my excel version.
– Shivam Shrivastava
Nov 22 '18 at 8:35
add a comment |
No need for complex AGGREGATEs or nasty array formulas.
A nice, simple MAXIFS will do what you want:
=MAXIFS($C$2:$C$7,$A$2:$A$7,$E$2,$B$2:$B$7,$F$2)
answered Nov 21 '18 at 17:20
GravitateGravitate
1,4171326
Thanks for your reply. But MAXIFS function is not available in my excel version.
– Shivam Shrivastava
Nov 22 '18 at 8:35
add a comment |
No need for complex AGGREGATEs or nasty array formulas.
A nice, simple MAXIFS will do what you want:
=MAXIFS($C$2:$C$7,$A$2:$A$7,$E$2,$B$2:$B$7,$F$2)
answered Nov 21 '18 at 17:20
GravitateGravitate
1,4171326
No need for complex AGGREGATEs or nasty array formulas.
A nice, simple MAXIFS will do what you want:
=MAXIFS($C$2:$C$7,$A$2:$A$7,$E$2,$B$2:$B$7,$F$2)
answered Nov 21 '18 at 17:20
GravitateGravitate
1,4171326
answered Nov 21 '18 at 17:20
GravitateGravitate
1,4171326
answered Nov 21 '18 at 17:20
GravitateGravitate
1,4171326
answered Nov 21 '18 at 17:20
GravitateGravitate
1,4171326
1,4171326
Thanks for your reply. But MAXIFS function is not available in my excel version.
– Shivam Shrivastava
Nov 22 '18 at 8:35
add a comment |
Thanks for your reply. But MAXIFS function is not available in my excel version.
– Shivam Shrivastava
Nov 22 '18 at 8:35
Thanks for your reply. But MAXIFS function is not available in my excel version.
– Shivam Shrivastava
Nov 22 '18 at 8:35
Thanks for your reply. But MAXIFS function is not available in my excel version.
– Shivam Shrivastava
Nov 22 '18 at 8:35
add a comment |
I would do this with an array formula (ctrl+shift+enter) :=MAX((--(A1:A6="X."))*(--(B1:B6="I."))*(C1:C6))
Explanation:
- the
(A1:A6="X.")results in an array withTRUE/FALSEvalues where the condition is met; - the
--converts this in1/0 - the first
*(array multiplication) gives an array with1/0where both conditions are met; - the second
*gives an array with all values from column C that corresponds with the rows where both conditions are met; - the
MAXresults then in the maximum.
Just a tricky part with the dot behind the X and I. Not sure whether this is part of your data, if not you of course have to modify the condition. And instead of hard coding it in the formula, much better to work with cell reference.
answered Nov 21 '18 at 13:12
Peter K.Peter K.
763212
add a comment |
I would do this with an array formula (ctrl+shift+enter) :=MAX((--(A1:A6="X."))*(--(B1:B6="I."))*(C1:C6))
Explanation:
- the
(A1:A6="X.")results in an array withTRUE/FALSEvalues where the condition is met; - the
--converts this in1/0 - the first
*(array multiplication) gives an array with1/0where both conditions are met; - the second
*gives an array with all values from column C that corresponds with the rows where both conditions are met; - the
MAXresults then in the maximum.
Just a tricky part with the dot behind the X and I. Not sure whether this is part of your data, if not you of course have to modify the condition. And instead of hard coding it in the formula, much better to work with cell reference.
answered Nov 21 '18 at 13:12
Peter K.Peter K.
763212
add a comment |
I would do this with an array formula (ctrl+shift+enter) :=MAX((--(A1:A6="X."))*(--(B1:B6="I."))*(C1:C6))
Explanation:
- the
(A1:A6="X.")results in an array withTRUE/FALSEvalues where the condition is met; - the
--converts this in1/0 - the first
*(array multiplication) gives an array with1/0where both conditions are met; - the second
*gives an array with all values from column C that corresponds with the rows where both conditions are met; - the
MAXresults then in the maximum.
Just a tricky part with the dot behind the X and I. Not sure whether this is part of your data, if not you of course have to modify the condition. And instead of hard coding it in the formula, much better to work with cell reference.
answered Nov 21 '18 at 13:12
Peter K.Peter K.
763212
I would do this with an array formula (ctrl+shift+enter) :=MAX((--(A1:A6="X."))*(--(B1:B6="I."))*(C1:C6))
Explanation:
- the
(A1:A6="X.")results in an array withTRUE/FALSEvalues where the condition is met; - the
--converts this in1/0 - the first
*(array multiplication) gives an array with1/0where both conditions are met; - the second
*gives an array with all values from column C that corresponds with the rows where both conditions are met; - the
MAXresults then in the maximum.
Just a tricky part with the dot behind the X and I. Not sure whether this is part of your data, if not you of course have to modify the condition. And instead of hard coding it in the formula, much better to work with cell reference.
answered Nov 21 '18 at 13:12
Peter K.Peter K.
763212
edited Nov 23 '18 at 12:40
answered Nov 21 '18 at 13:12
Peter K.Peter K.
763212
answered Nov 21 '18 at 13:12
Peter K.Peter K.
763212
answered Nov 21 '18 at 13:12
Peter K.Peter K.
763212
763212
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- 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%2fstackoverflow.com%2fquestions%2f53412523%2fgetting-minimum-value-in-a-range-with-multiple-condition%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
What have you tried please? There are many examples of Max Ifs on SO. You may also be able to use a pivottable. Your title says minimum and question body says max - though same principle will apply.
– QHarr
Nov 21 '18 at 13:05
use the following formula to determine if your date time values in C are actually numbers or strings representing a date.
=isnumber(C2)a result of true means its a number which means it will not need to be converted.– Forward Ed
Nov 21 '18 at 14:52