Forum Discussion
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 material and plant.
For. eg.
| Material | Plant | Vendor | Total Spend Impact |
| 11059512 | X | A11 | 27060 |
| 11059512 | X | A11 | 2225000 |
| 11059512 | X | A11 | 113842 |
| 11059512 | X | A11 | 73800 |
| 11059512 | X | A11 | 41400 |
| 11514445 | Y | E02 | 30236 |
| 11514445 | Y | E02 | 30177 |
| 11532719 | Y | E02 | 88243 |
| 67422090 | Y | R01 | 103047 |
| 67422090 | Y | 50552356 | 103047 |
Formula =MEDIAN((D2:D11)*--(A2:A11=A2)*--(B2:B11=B2))
I'm trying to execute this formula but it evaluate result it returns an array: median({27060:2225000:113842:73800:41400:0:0:0:0:0)}.
Since it is also considering 0 to calculate median, answer is coming wrong. How do I remove zero from my array list and only consider number.
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
6 Replies
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.
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
- Thank you Hans! its working..
