Excel: Return value based on lowest score
up vote
0
down vote
favorite
I have a database that collects profits from different stores on a weekly basis:
Database Sample
Each week, I need to show which store had the lowest profit in each state.
Output Needed
I'd need to show the name of the site, and also what its profit was for that week.
Could anyone help me? Thank you :)
microsoft-excel
asked Nov 14 at 9:08
Anniesoptera
12
add a comment |
up vote
0
down vote
favorite
I have a database that collects profits from different stores on a weekly basis:
Database Sample
Each week, I need to show which store had the lowest profit in each state.
Output Needed
I'd need to show the name of the site, and also what its profit was for that week.
Could anyone help me? Thank you :)
microsoft-excel
asked Nov 14 at 9:08
Anniesoptera
12
1
1) Copy your sample data into question (as a text) and format it (as it is a code); 2) Show desired result (data, not form only) on that source data. 3) And what you want to obtain if 2 or more sites provide the same minimal profit?
– Akina
Nov 14 at 10:35
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I have a database that collects profits from different stores on a weekly basis:
Database Sample
Each week, I need to show which store had the lowest profit in each state.
Output Needed
I'd need to show the name of the site, and also what its profit was for that week.
Could anyone help me? Thank you :)
microsoft-excel
asked Nov 14 at 9:08
Anniesoptera
12
I have a database that collects profits from different stores on a weekly basis:
Database Sample
Each week, I need to show which store had the lowest profit in each state.
Output Needed
I'd need to show the name of the site, and also what its profit was for that week.
Could anyone help me? Thank you :)
microsoft-excel
microsoft-excel
asked Nov 14 at 9:08
Anniesoptera
12
asked Nov 14 at 9:08
Anniesoptera
12
asked Nov 14 at 9:08
Anniesoptera
12
asked Nov 14 at 9:08
Anniesoptera
12
asked Nov 14 at 9:08
Anniesoptera
12
12
1
1) Copy your sample data into question (as a text) and format it (as it is a code); 2) Show desired result (data, not form only) on that source data. 3) And what you want to obtain if 2 or more sites provide the same minimal profit?
– Akina
Nov 14 at 10:35
add a comment |
1
1) Copy your sample data into question (as a text) and format it (as it is a code); 2) Show desired result (data, not form only) on that source data. 3) And what you want to obtain if 2 or more sites provide the same minimal profit?
– Akina
Nov 14 at 10:35
1
1
1) Copy your sample data into question (as a text) and format it (as it is a code); 2) Show desired result (data, not form only) on that source data. 3) And what you want to obtain if 2 or more sites provide the same minimal profit?
– Akina
Nov 14 at 10:35
1) Copy your sample data into question (as a text) and format it (as it is a code); 2) Show desired result (data, not form only) on that source data. 3) And what you want to obtain if 2 or more sites provide the same minimal profit?
– Akina
Nov 14 at 10:35
add a comment |
1 Answer
1
active
oldest
votes
up vote
1
down vote
Good Day Anniesoptera,
Assuming I understand your question right I would solve accomplish this with a combination of 3 Excel formulas. MIN, INDEX & MATCH.
- Sort the data by state
Assuming your information is in cells A1-C14, use the =MIN formula to
determine which the lowest profit for the state is.
So for California (MIN(C2:C5)Use this formula in the MATCH Formula
(MATCH((MIN(22:C5),C2:5,0). This will give the row which this figure is in
Add this formula to the INDEX Formula to obtain your site
=INDEX(A2:C5,(MATCH((MIN(C2:C5)),C2:C5,0)),1).
This can then be done again to obtain the amount change the final value in the formula
from a 1 to 3 (The column number).
=INDEX(A2:C5,(MATCH((MIN(C2:C5)),C2:C5,0)),3)
I hope this helps.
answered Nov 14 at 14:22
BradR
364
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
1
down vote
Good Day Anniesoptera,
Assuming I understand your question right I would solve accomplish this with a combination of 3 Excel formulas. MIN, INDEX & MATCH.
- Sort the data by state
Assuming your information is in cells A1-C14, use the =MIN formula to
determine which the lowest profit for the state is.
So for California (MIN(C2:C5)Use this formula in the MATCH Formula
(MATCH((MIN(22:C5),C2:5,0). This will give the row which this figure is in
Add this formula to the INDEX Formula to obtain your site
=INDEX(A2:C5,(MATCH((MIN(C2:C5)),C2:C5,0)),1).
This can then be done again to obtain the amount change the final value in the formula
from a 1 to 3 (The column number).
=INDEX(A2:C5,(MATCH((MIN(C2:C5)),C2:C5,0)),3)
I hope this helps.
answered Nov 14 at 14:22
BradR
364
add a comment |
up vote
1
down vote
Good Day Anniesoptera,
Assuming I understand your question right I would solve accomplish this with a combination of 3 Excel formulas. MIN, INDEX & MATCH.
- Sort the data by state
Assuming your information is in cells A1-C14, use the =MIN formula to
determine which the lowest profit for the state is.
So for California (MIN(C2:C5)Use this formula in the MATCH Formula
(MATCH((MIN(22:C5),C2:5,0). This will give the row which this figure is in
Add this formula to the INDEX Formula to obtain your site
=INDEX(A2:C5,(MATCH((MIN(C2:C5)),C2:C5,0)),1).
This can then be done again to obtain the amount change the final value in the formula
from a 1 to 3 (The column number).
=INDEX(A2:C5,(MATCH((MIN(C2:C5)),C2:C5,0)),3)
I hope this helps.
answered Nov 14 at 14:22
BradR
364
add a comment |
up vote
1
down vote
up vote
1
down vote
Good Day Anniesoptera,
Assuming I understand your question right I would solve accomplish this with a combination of 3 Excel formulas. MIN, INDEX & MATCH.
- Sort the data by state
Assuming your information is in cells A1-C14, use the =MIN formula to
determine which the lowest profit for the state is.
So for California (MIN(C2:C5)Use this formula in the MATCH Formula
(MATCH((MIN(22:C5),C2:5,0). This will give the row which this figure is in
Add this formula to the INDEX Formula to obtain your site
=INDEX(A2:C5,(MATCH((MIN(C2:C5)),C2:C5,0)),1).
This can then be done again to obtain the amount change the final value in the formula
from a 1 to 3 (The column number).
=INDEX(A2:C5,(MATCH((MIN(C2:C5)),C2:C5,0)),3)
I hope this helps.
answered Nov 14 at 14:22
BradR
364
Good Day Anniesoptera,
Assuming I understand your question right I would solve accomplish this with a combination of 3 Excel formulas. MIN, INDEX & MATCH.
- Sort the data by state
Assuming your information is in cells A1-C14, use the =MIN formula to
determine which the lowest profit for the state is.
So for California (MIN(C2:C5)Use this formula in the MATCH Formula
(MATCH((MIN(22:C5),C2:5,0). This will give the row which this figure is in
Add this formula to the INDEX Formula to obtain your site
=INDEX(A2:C5,(MATCH((MIN(C2:C5)),C2:C5,0)),1).
This can then be done again to obtain the amount change the final value in the formula
from a 1 to 3 (The column number).
=INDEX(A2:C5,(MATCH((MIN(C2:C5)),C2:C5,0)),3)
I hope this helps.
answered Nov 14 at 14:22
BradR
364
answered Nov 14 at 14:22
BradR
364
answered Nov 14 at 14:22
BradR
364
answered Nov 14 at 14:22
BradR
364
364
add a comment |
add a comment |
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%2f1375243%2fexcel-return-value-based-on-lowest-score%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
1) Copy your sample data into question (as a text) and format it (as it is a code); 2) Show desired result (data, not form only) on that source data. 3) And what you want to obtain if 2 or more sites provide the same minimal profit?
– Akina
Nov 14 at 10:35