0
207
ViewsFinding maximum value in group
I want to create a column of data that finds the largest value in column BD, based on individual values in Column B. I would have thought this equation would work! Anyone have any recommendations??
(first attempt) =IF(BD3=0,0,SUMIFS($BD$3:$BD3,$B$3:$B3,B3,$BD$3:$BD3,MAX($BD$3:$BD3)))
(Second attempt) =IF(BD3=MAXIFS($BD$3:$BD3,$B$3:$B3,B3),MAXIFS($BD$3:$BD3,$B$3:$B3,B3),0)
| Projectid(B) | cumulative production(BD) | result I want() |
|---|---|---|
| 1 | 20 | 0 |
| 1 | 60 | 0 |
| 1 | 70 | 70 |
| 2 | 0 | 0 |
| 2 | 0 | 0 |
| 3 | 20 | 20 |
| 4 | 0 | 0 |
| 5 | 0 | 0 |
| 6 | 0 | 0 |
| 7 | 10 | 0 |
| 7 | 40 | 0 |
| 7 | 60 | 60 |
3 answers
Answer question0
this code should work:
=IF(MAXIFS($BD$1:$BD$12,$B$1:$B$12,A1)=BD1,MAXIFS($BD$1:$BD$12,$B$1:$B$12,B1),0)
0
Similar to SUMIFS you find MAXIFS
https://support.microsoft.com/en-us/office/maxifs-function-dfd611e6-da2c-488a-919b-9b6376b28883
Then just compare the MAX with the actual row value.
Edit based on your reply:
One way is to start with one operation in the cell first then it is easier to follow.
Your MAXIF should be like this (looking for the max in column B of those rows having the same value in column A as the value of current column A)
=MAXIFS(B$2:B$13;A$2:A$13;A2)
Then compare to B2
So, basically you got it, need just to adjust your second part of the MAXIFS :-)
0
If you have access to LAMBDA() function then can use below formula at one go.
=LET(a,BYROW(A2:A13,LAMBDA(x,MAXIFS(B2:B13,A2:A13,x))),IF(B2:B13=a,a,0))
