Forum Discussion
Solved
Finding error while calculate median for a Groupby - array returns zero with other values.
I'm trying to calculate a median value at group by operation but not finding correct answer. In my example i've material and plant information and want to know the total spend median value at each ma...
Try
=MEDIAN(IF(($A$2:$A$11=A2)*($B$2:$B$11=B2),$D$2:$D$11))
If you don't have Microsoft 365 or Office 2021, confirm the formula by pressing Ctrl+Shift+Enter
As variant
=AGGREGATE(17,6, D2:D11/($A$2:$A$11=A2)/($B$2:$B$11=B2), 2)used as regular formula on pre-DA Excel.
SergeiBaklan Thanks for helping to calculate Median. Could you also tell me if the aggregate function can also work for sum, average, count, etc with group by operation?
I'm trying for sum but getting error..
=AGGREGATE(9,6,$D$2:$D$11/(($A$2:$A$11=A2)*($B$2:$B$11=B2)))
Thanks in advance! 🙂
No, AGGREGATE() works with arrays only starting from function #14. All functions before work only with ranges.
- Thanks