0

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 :)

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
    Commented Nov 14, 2018 at 10:35

1 Answer 1

1

Good Day Anniesoptera,

Assuming I understand your question right I would solve accomplish this with a combination of 3 Excel formulas. MIN, INDEX & MATCH.

  1. Sort the data by state
  2. 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)

  3. Use this formula in the MATCH Formula (MATCH((MIN(22:C5),C2:5,0). This will give the row which this figure is in

  4. 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)

Screen Cap of the spreadsheet with a complete formula

I hope this helps.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .