How to get min value in a group?

Question

Example:

ID  Value   MAX
Group1  2   6
Group1  4   6
Group1  6   6
Group2  1   3
Group2  3   3
Group3  7   8
Group3  4   8
Group3  2   8
Group3  8   8
Group4  1   3
Group4  2   3
Group4  3   3
Group5  7   7

The column 'MAX' has the results I want.

My two part question is:

(1) How can I get the values for the 'Max' column?

I am currently using a pivot table but users complain it is too slow and can make Excel non-responsive.

I tried to use array functions with the formula like this:

=MAX(IF($A$9:$A$21=A12,$B$9:$B$21))

This doesn't stay current and I need some mechanisms to refresh the data. Users said they don't want yet-another-button to refresh the data.

(2) Assuming there is a formula to solve the above, my Value column is a date which could be empty, and my requirement is also to get the minimum date in the group, ignoring any blanks.

Answer 1

In C2 enter the array formula:

=MAX(IF(A:A=A2,B:B))  

and copy down.

Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key. If this is done correctly, the formula will appear with curly braces around it in the Formula Bar.