Determine minimum value based on several criteria

[MissAudrey]

I am trying to create a formula in excel to determine the minimum value in column C based on the following:

1. Find all rows where column A = Mark.
2. Find all rows = Mark, determine the maximum value in Column B.
3. For the maximum column B values associated with Mark, determine the minimum value in Column C.

Answer = 200



Column A Column B Column C
Mark 6 300
Mark 6 200
John 6 10
Mark 5 3

I know I could sort my data for each of the three columns and use vlookup. I am trying to come up with a formula in which I do not need to sort the data.

[Pete_UK]

Try this array* formula:

=MIN(IF((A$1:A$4="Mark")*(B$1:B$4=MAX(B$1:B$4)),C$1:C$4))

*An array formula must be committed using the key combination of Ctrl-Shift-Enter (CSE) instead of the usual <Enter>.

Hope this helps.

Pete

[Tony Valko]

Try this...

Data Range

	A	B	C	D	E	F
1	Mark	6	300		Mark	200
2	Mark	6	200
3	John	6	10
4	Mark	5	3
5	------	------	------	------	------	------

This array formula** entered in F1:

=MIN(IF(A1:A4=E1,IF(B1:B4=MAX(IF(A1:A4=E1,B1:B4)),C1:C4)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[Tony Valko]

With this data:

Data Range

	A	B	C
1	Mark	6	300
2	Mark	6	200
3	John	10	10
4	Mark	5	3
5	------	------	------

My formula returns 200 and yours returns 0.

I interpreted this condition:

2. Find all rows = Mark, determine the maximum value in Column B.

To mean: max in column B if column A = Mark

[MissAudrey]

Tony's Response worked for me! Thank you!

I have two additional scenarios that I am trying to add to the formula which I have not had any luck in solving.

1. In cell F2, I would like to add another formula that provides the value in column B associated with the minimum calculated with this formula:

=MIN(IF(A1:A4=E1,IF(B1:B4=MAX(IF(A1:A4=E1,B1:B4)),C1:C4)))

In the example above, the formula result is located in row 2 of column C. I want to create another formula to give me the result in column B for this same row (cell B2).

2. If the name entered into E1 is not found in A1:A4, I want it to return "Error". Example: Replace cell E1 with the name Sam.

[Tony Valko]

Can you post a SMALL sample file and show us what result you expect?

A SMALL file will have about 20 rows worth of data.

[MissAudrey]

I have uploaded a simplified example of what I am trying to do.

[Tony Valko]

All formulas are array formulas**.

For Q1 amount...

=INDEX(B2:B5,MATCH(1,(A2:A5=E2)*(C2:C5=F2),0))

For Q2 amount...

=IF(COUNTIF(A2:A5,E2),MIN(IF(A2:A5=E2,IF(B2:B5=MAX(IF(A2:A5=E2,B2:B5)),C2:C5))),"No Entry")

For Q2 rank...

=IFERROR(INDEX(B2:B5,MATCH(1,(A2:A5=E2)*(C2:C5=F2),0)),"No Entry")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[Aladin Akyurek]

Set up with AGGREGATE...

F2, just enter:

=IF(COUNTIF(A2:A5,E2),
AGGREGATE(15,6,C2:C5/((A2:A5=E2)*(B2:B5=AGGREGATE(14,6,B2:B5/(A2:A5=E2),1))),1),"No entry")

F3, just enter:

=IF(F2="No entry","No entry",AGGREGATE(15,6,$B$2:$B$5/((A2:A5=E2)*(C2:C5=H2)),1))

F6, just enter:

=IF(COUNTIF(A2:A5,E6),
AGGREGATE(15,6,C2:C5/((A2:A5=E6)*(B2:B5=AGGREGATE(14,6,B2:B5/(A2:A5=E6),1))),1),"No entry")

F7, just enter:

=IF(F6="No entry","No entry",AGGREGATE(15,6,$B$2:$B$5/((A2:A5=E2)*(C2:C5=H2)),1))

[Tony Valko]

On my machine the array formulas are slightly faster to calculate compared to the equivalent AGGREGATE formula.

The difference would be considered negligible but the array formula syntax is easier to construct and understand.

[MissAudrey]

This worked for me. Thank you very much.

I am new to arrays as well as the functions used in the formulas below. I would really appreciate if you could walk me through how each formula works so that I can learn for future applications.

Thanks again!

For Q1 amount...

=INDEX(B2:B5,MATCH(1,(A2:A5=E2)*(C2:C5=F2),0))

For Q2 amount...

=IF(COUNTIF(A2:A5,E2),MIN(IF(A2:A5=E2,IF(B2:B5=MAX(IF(A2:A5=E2,B2:B5)),C2:C5))),"No Entry")

For Q2 rank...

=IFERROR(INDEX(B2:B5,MATCH(1,(A2:A5=E2)*(C2:C5=F2),0)),"No Entry")

[MissAudrey]

I was able to successfully use Tony's suggestion above.

I now have an additional scenario to add to the scenario. See attachment.

I am new to arrays and I am not interpretting the formulas correctly above to be able to apply it to my new scenario. Any help in interpretting would be greatly appreciated.Sample2a.xlsx

[Tony Valko]

Try this array formula**:

G2 = A

=MIN(IF(A2:A8=G2,IF(B2:B8=MAX(IF(A2:A8=G2,B2:B8)),IF(C2:C8=MAX(IF(B2:B8=MAX(IF(A2:A8=G2,B2:B8)),C2:C8)),D2:D8))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[MissAudrey]

Tony - I was able to get the correct answer using your suggestion above using the spreadsheet I had attached which is a simplied version of my data (Sample 2a).

When I tried to apply the formula to my actual data set I have some situations where the answer is correct but other times I am receiving an answer of zero. I can't figure out why it works in some situations and not others.
I have uploaded the example file (file - Sample2b.xlsxSample 2b).

Product Tab - Data is entered.
Info Tab - Calculations are performed.

[Tony Valko]

I took a couple of days off...

Try this array formula**:

=MIN(IF((Product!X$2:X$89=AJ8)*(Product!O$2:O$89=MAX(IF(Product!X$2:X$89=AJ8,Product!O$2:O$89)))*(Product!K$2:K$89=MAX(IF(Product!X$2:X$89=AJ8,IF(Product!O$2:O$89=MAX(IF(Product!X$2:X$89=AJ8,Product!O$2:O$89)),Product!K$2:K$89)))),Product!J$2:J$89))

Copy down as needed.

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.