I have a table containing columns like category and values
I want to find the median of individual category I tried the "=median and if formula", it is working if I have two categories only if I have 3 like below data it does work, I want to do this without using pivot table actually that's a constrain
| Categories | Values |
|---|---|
| a | 5 |
| b | 4 |
| c | 9 |
| c | 10 |
| b | 6 |
| a | 2 |
| c | 11 |
| b | 7 |
| a | 3 |
| b | 8 |
I tried to formulate my requirement on the table but I guess there is some problem with the formula here
=IF(A2:A11="a",MEDIAN(B2:B11),IF(A2:A11="b",MEDIAN(B2:B11),IF(A2:A11="c",MEDIAN(B2:B11))))
Apart from this I tried the pivot table as well by using add measure it worked but I don't know why is it not working through formula
CodePudding user response：
Your condition part of the if statement is returning an array. Don't use IF()...
You need to filter the column A.
=MEDIAN(FILTER(B1:B11,A1:A11="a"))
=MEDIAN(FILTER(B1:B11,A1:A11="b"))
=MEDIAN(FILTER(B1:B11,A1:A11="c"))
CodePudding user response：
Try the following formula for spill down array. You do not need to drag down, no need to put formula to individual cells.
=LET(x,UNIQUE(A2:A11),y,MAP(x,LAMBDA(a,MEDIAN(FILTER(B2:B11,A2:A11=a)))),HSTACK(x,y))
CodePudding user response：
if you want to drag the formula then take unique categories and apply the formula.. in F2 cell
=MEDIAN(IF($B$2:$B$11=E2,$C$2:$C$11,""))