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