Menu
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.
642
The MIN() aggregate function returns the lowest value (minimum) in a set of non-NULL values.
The largest value in a data set is often called the maximum (or max for short), and the smallest value is called the minimum (or min). The difference between the maximum and minimum value is sometimes called the range and is calculated by subtracting the smallest value from the largest value.
you can find a row-wise minimum like this: SELECT C1, C2, C3, ( SELECT MIN(C) FROM (VALUES (C1), (C2), (C3) AS v (C) ) AS MinC FROM T ; Basically you are arranging the values of C1 , C2 , C3 as a column and are applying a normal (column-wise) aggregate function to it to find the minimum.
To select data where a field has min value, you can use aggregate function min(). The syntax is as follows. SELECT *FROM yourTableName WHERE yourColumnName=(SELECT MIN(yourColumnName) FROM yourTableName);
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.
145
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
