finding the cheaper price in excel
-1
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
177
add a comment |
-1
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
177
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
-1
-1
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
177
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
177
asked Nov 21 '18 at 14:11
ravenraven
177
asked Nov 21 '18 at 14:11
ravenraven
177
asked Nov 21 '18 at 14:11
ravenraven
177
asked Nov 21 '18 at 14:11
ravenraven
177
177
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
0
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
19310
works well, tks @extensionhelp
– raven
Nov 21 '18 at 14:54
add a comment |
0
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,113129
it works well @usmanhaq
– raven
Nov 21 '18 at 14:54
add a comment |
0
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,3921326
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
});
}
});
draft saved
draft discarded
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
0
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
19310
works well, tks @extensionhelp
– raven
Nov 21 '18 at 14:54
add a comment |
0
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
19310
works well, tks @extensionhelp
– raven
Nov 21 '18 at 14:54
add a comment |
0
0
0
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
19310
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
19310
answered Nov 21 '18 at 14:31
extensionhelpextensionhelp
19310
answered Nov 21 '18 at 14:31
extensionhelpextensionhelp
19310
answered Nov 21 '18 at 14:31
extensionhelpextensionhelp
19310
19310
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 |
0
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,113129
it works well @usmanhaq
– raven
Nov 21 '18 at 14:54
add a comment |
0
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,113129
it works well @usmanhaq
– raven
Nov 21 '18 at 14:54
add a comment |
0
0
0
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,113129
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,113129
answered Nov 21 '18 at 14:33
usmanhaqusmanhaq
1,113129
answered Nov 21 '18 at 14:33
usmanhaqusmanhaq
1,113129
answered Nov 21 '18 at 14:33
usmanhaqusmanhaq
1,113129
1,113129
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 |
0
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,3921326
this works too ! tks @gravitate
– raven
Nov 22 '18 at 9:32
add a comment |
0
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,3921326
this works too ! tks @gravitate
– raven
Nov 22 '18 at 9:32
add a comment |
0
0
0
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,3921326
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,3921326
answered Nov 21 '18 at 17:04
GravitateGravitate
1,3921326
answered Nov 21 '18 at 17:04
GravitateGravitate
1,3921326
answered Nov 21 '18 at 17:04
GravitateGravitate
1,3921326
1,3921326
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 |
draft saved
draft discarded
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.
draft saved
draft discarded
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