Home > Back-end >  Finding maximum value in group
Finding maximum value in group

Time:06-23

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

CodePudding user response:

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)

CodePudding user response:

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

enter image description here

CodePudding user response:

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 :-)

  • Related