finding the cheaper price in excel
I have a table in the following format
ID Price
123 £12.99
123 £13.00
123 £12.99
456 £21.99
456 £20.90
789 £18.99
789 £16.99
I'm trying to find the cheaper price based on the ID and the expected output should be in the following format
ID Price Cheap
123 £12.99 Cheaper
123 £13.00
123 £12.99 Cheaper
456 £21.99
456 £20.90 Cheaper
789 £18.99
789 £16.99 Cheaper
I'd like to know how to implement this in excel/googlesheets?
excel google-sheets
asked Nov 21 '18 at 14:11
ravenraven
81
add a comment |
I have a table in the following format
ID Price
123 £12.99
123 £13.00
123 £12.99
456 £21.99
456 £20.90
789 £18.99
789 £16.99
I'm trying to find the cheaper price based on the ID and the expected output should be in the following format
ID Price Cheap
123 £12.99 Cheaper
123 £13.00
123 £12.99 Cheaper
456 £21.99
456 £20.90 Cheaper
789 £18.99
789 £16.99 Cheaper
I'd like to know how to implement this in excel/googlesheets?
excel google-sheets
asked Nov 21 '18 at 14:11
ravenraven
81
1
TheMINfunction? Or, a pivot table.
– ashleedawg
Nov 21 '18 at 14:13
The question is unclear as Excel and Google Sheets could have several ways to achieve the same result.
– Rubén
Nov 22 '18 at 2:34
If you found any of the answers helpful, feel free to say thanks by up voting them. If any of them have solved your problem, please mark the one you consider the best, as the correct answer by clicking the grey tick next to the answer.
– Gravitate
Nov 22 '18 at 13:18
add a comment |
I have a table in the following format
ID Price
123 £12.99
123 £13.00
123 £12.99
456 £21.99
456 £20.90
789 £18.99
789 £16.99
I'm trying to find the cheaper price based on the ID and the expected output should be in the following format
ID Price Cheap
123 £12.99 Cheaper
123 £13.00
123 £12.99 Cheaper
456 £21.99
456 £20.90 Cheaper
789 £18.99
789 £16.99 Cheaper
I'd like to know how to implement this in excel/googlesheets?
excel google-sheets
asked Nov 21 '18 at 14:11
ravenraven
81
I have a table in the following format
ID Price
123 £12.99
123 £13.00
123 £12.99
456 £21.99
456 £20.90
789 £18.99
789 £16.99
I'm trying to find the cheaper price based on the ID and the expected output should be in the following format
ID Price Cheap
123 £12.99 Cheaper
123 £13.00
123 £12.99 Cheaper
456 £21.99
456 £20.90 Cheaper
789 £18.99
789 £16.99 Cheaper
I'd like to know how to implement this in excel/googlesheets?
excel google-sheets
excel google-sheets
asked Nov 21 '18 at 14:11
ravenraven
81
asked Nov 21 '18 at 14:11
ravenraven
81
asked Nov 21 '18 at 14:11
ravenraven
81
asked Nov 21 '18 at 14:11
ravenraven
81
asked Nov 21 '18 at 14:11
ravenraven
81
81
1
TheMINfunction? Or, a pivot table.
– ashleedawg
Nov 21 '18 at 14:13
The question is unclear as Excel and Google Sheets could have several ways to achieve the same result.
– Rubén
Nov 22 '18 at 2:34
If you found any of the answers helpful, feel free to say thanks by up voting them. If any of them have solved your problem, please mark the one you consider the best, as the correct answer by clicking the grey tick next to the answer.
– Gravitate
Nov 22 '18 at 13:18
add a comment |
1
TheMINfunction? Or, a pivot table.
– ashleedawg
Nov 21 '18 at 14:13
The question is unclear as Excel and Google Sheets could have several ways to achieve the same result.
– Rubén
Nov 22 '18 at 2:34
If you found any of the answers helpful, feel free to say thanks by up voting them. If any of them have solved your problem, please mark the one you consider the best, as the correct answer by clicking the grey tick next to the answer.
– Gravitate
Nov 22 '18 at 13:18
1
1
The
MIN function? Or, a pivot table.– ashleedawg
Nov 21 '18 at 14:13
The
MIN function? Or, a pivot table.– ashleedawg
Nov 21 '18 at 14:13
The question is unclear as Excel and Google Sheets could have several ways to achieve the same result.
– Rubén
Nov 22 '18 at 2:34
The question is unclear as Excel and Google Sheets could have several ways to achieve the same result.
– Rubén
Nov 22 '18 at 2:34
If you found any of the answers helpful, feel free to say thanks by up voting them. If any of them have solved your problem, please mark the one you consider the best, as the correct answer by clicking the grey tick next to the answer.
– Gravitate
Nov 22 '18 at 13:18
If you found any of the answers helpful, feel free to say thanks by up voting them. If any of them have solved your problem, please mark the one you consider the best, as the correct answer by clicking the grey tick next to the answer.
– Gravitate
Nov 22 '18 at 13:18
add a comment |
3 Answers
3
active
oldest
votes
You can use a MIN IF array function in Excel for this. The formula I entered in C2 is:
=IF(B2=MIN(IF(A$2:A$8=A2,B$2:B$8,"")),"Cheaper","")
Note that because it's an array formula, it must be committed with CTRL + SHIFT + ENTER.
answered Nov 21 '18 at 14:31
extensionhelpextensionhelp
2407
works well, tks @extensionhelp
– raven
Nov 21 '18 at 14:54
add a comment |
For the following setup
Formula in C2 is
=ArrayFormula(IF(B2=MIN(IF(A2=$A$2:$A$8,$B$2:$B$8)),"Cheaper",""))
answered Nov 21 '18 at 14:33
usmanhaqusmanhaq
1,123128
it works well @usmanhaq
– raven
Nov 21 '18 at 14:54
add a comment |
No need for nasty array formulas here:
=IF($B2=MINIFS($B$2:$B$8,$A$2:$A$8,$A2),"Cheaper","")
answered Nov 21 '18 at 17:04
GravitateGravitate
1,2031225
this works too ! tks @gravitate
– raven
Nov 22 '18 at 9:32
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%2f53413960%2ffinding-the-cheaper-price-in-excel%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
You can use a MIN IF array function in Excel for this. The formula I entered in C2 is:
=IF(B2=MIN(IF(A$2:A$8=A2,B$2:B$8,"")),"Cheaper","")
Note that because it's an array formula, it must be committed with CTRL + SHIFT + ENTER.
answered Nov 21 '18 at 14:31
extensionhelpextensionhelp
2407
works well, tks @extensionhelp
– raven
Nov 21 '18 at 14:54
add a comment |
You can use a MIN IF array function in Excel for this. The formula I entered in C2 is:
=IF(B2=MIN(IF(A$2:A$8=A2,B$2:B$8,"")),"Cheaper","")
Note that because it's an array formula, it must be committed with CTRL + SHIFT + ENTER.
answered Nov 21 '18 at 14:31
extensionhelpextensionhelp
2407
works well, tks @extensionhelp
– raven
Nov 21 '18 at 14:54
add a comment |
You can use a MIN IF array function in Excel for this. The formula I entered in C2 is:
=IF(B2=MIN(IF(A$2:A$8=A2,B$2:B$8,"")),"Cheaper","")
Note that because it's an array formula, it must be committed with CTRL + SHIFT + ENTER.
answered Nov 21 '18 at 14:31
extensionhelpextensionhelp
2407
You can use a MIN IF array function in Excel for this. The formula I entered in C2 is:
=IF(B2=MIN(IF(A$2:A$8=A2,B$2:B$8,"")),"Cheaper","")
Note that because it's an array formula, it must be committed with CTRL + SHIFT + ENTER.
answered Nov 21 '18 at 14:31
extensionhelpextensionhelp
2407
answered Nov 21 '18 at 14:31
extensionhelpextensionhelp
2407
answered Nov 21 '18 at 14:31
extensionhelpextensionhelp
2407
answered Nov 21 '18 at 14:31
extensionhelpextensionhelp
2407
2407
works well, tks @extensionhelp
– raven
Nov 21 '18 at 14:54
add a comment |
works well, tks @extensionhelp
– raven
Nov 21 '18 at 14:54
works well, tks @extensionhelp
– raven
Nov 21 '18 at 14:54
works well, tks @extensionhelp
– raven
Nov 21 '18 at 14:54
add a comment |
For the following setup
Formula in C2 is
=ArrayFormula(IF(B2=MIN(IF(A2=$A$2:$A$8,$B$2:$B$8)),"Cheaper",""))
answered Nov 21 '18 at 14:33
usmanhaqusmanhaq
1,123128
it works well @usmanhaq
– raven
Nov 21 '18 at 14:54
add a comment |
For the following setup
Formula in C2 is
=ArrayFormula(IF(B2=MIN(IF(A2=$A$2:$A$8,$B$2:$B$8)),"Cheaper",""))
answered Nov 21 '18 at 14:33
usmanhaqusmanhaq
1,123128
it works well @usmanhaq
– raven
Nov 21 '18 at 14:54
add a comment |
For the following setup
Formula in C2 is
=ArrayFormula(IF(B2=MIN(IF(A2=$A$2:$A$8,$B$2:$B$8)),"Cheaper",""))
answered Nov 21 '18 at 14:33
usmanhaqusmanhaq
1,123128
For the following setup
Formula in C2 is
=ArrayFormula(IF(B2=MIN(IF(A2=$A$2:$A$8,$B$2:$B$8)),"Cheaper",""))
answered Nov 21 '18 at 14:33
usmanhaqusmanhaq
1,123128
answered Nov 21 '18 at 14:33
usmanhaqusmanhaq
1,123128
answered Nov 21 '18 at 14:33
usmanhaqusmanhaq
1,123128
answered Nov 21 '18 at 14:33
usmanhaqusmanhaq
1,123128
1,123128
it works well @usmanhaq
– raven
Nov 21 '18 at 14:54
add a comment |
it works well @usmanhaq
– raven
Nov 21 '18 at 14:54
it works well @usmanhaq
– raven
Nov 21 '18 at 14:54
it works well @usmanhaq
– raven
Nov 21 '18 at 14:54
add a comment |
No need for nasty array formulas here:
=IF($B2=MINIFS($B$2:$B$8,$A$2:$A$8,$A2),"Cheaper","")
answered Nov 21 '18 at 17:04
GravitateGravitate
1,2031225
this works too ! tks @gravitate
– raven
Nov 22 '18 at 9:32
add a comment |
No need for nasty array formulas here:
=IF($B2=MINIFS($B$2:$B$8,$A$2:$A$8,$A2),"Cheaper","")
answered Nov 21 '18 at 17:04
GravitateGravitate
1,2031225
this works too ! tks @gravitate
– raven
Nov 22 '18 at 9:32
add a comment |
No need for nasty array formulas here:
=IF($B2=MINIFS($B$2:$B$8,$A$2:$A$8,$A2),"Cheaper","")
answered Nov 21 '18 at 17:04
GravitateGravitate
1,2031225
No need for nasty array formulas here:
=IF($B2=MINIFS($B$2:$B$8,$A$2:$A$8,$A2),"Cheaper","")
answered Nov 21 '18 at 17:04
GravitateGravitate
1,2031225
answered Nov 21 '18 at 17:04
GravitateGravitate
1,2031225
answered Nov 21 '18 at 17:04
GravitateGravitate
1,2031225
answered Nov 21 '18 at 17:04
GravitateGravitate
1,2031225
1,2031225
this works too ! tks @gravitate
– raven
Nov 22 '18 at 9:32
add a comment |
this works too ! tks @gravitate
– raven
Nov 22 '18 at 9:32
this works too ! tks @gravitate
– raven
Nov 22 '18 at 9:32
this works too ! tks @gravitate
– raven
Nov 22 '18 at 9:32
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2f53413960%2ffinding-the-cheaper-price-in-excel%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
1
The
MINfunction? Or, a pivot table.– ashleedawg
Nov 21 '18 at 14:13
The question is unclear as Excel and Google Sheets could have several ways to achieve the same result.
– Rubén
Nov 22 '18 at 2:34
If you found any of the answers helpful, feel free to say thanks by up voting them. If any of them have solved your problem, please mark the one you consider the best, as the correct answer by clicking the grey tick next to the answer.
– Gravitate
Nov 22 '18 at 13:18