Median in Pivot Table

[vicramirezb]

Can I calculate medians in a Pivot Table?. I been trying for days but nothing. Please let me know if I can not to stow trying.

Thanks

 

[Juan Pablo González]

Basically no. You can, however, reproduce this functionality by using some array formulas.

Something like this

=MEDIAN(IF((Range1=Condition1)*(Range2=Condition2)*(...),RangeToCalculate))

its not as fast as a pivot table, but works.

 

[thejd99]

I found this thread which I think has the solution I need. But, I need some help in understanding the formula. Inside the IF statement am I supposed to list (Range1=Condition1) for every possible condition? For example, If I had a list of products in A1:A50 & the amount produced by day in B1:B50, would the formula be
=MEDIAN(IF((A1:A50=ProductA)*(A1:A50=ProductB)*(...),B1:B50)

Thanks
Jeff

 

[twinetwstr]

Can I calculate medians in a Pivot Table?. I been trying for days but nothing. Please let me know if I can not to stow trying.

Thanks

Calculating the Median with a Pivot Table is quick and easy, if you have a little latitude with the data you are analysing. It works a whole lot faster and easier than those nested IF formulas everyone (including me) has been using. Here is the secret:

First, you need a field you can use as an Index for the Row Labels. There cannot be any duplications in this field, so if you need to, run a column of numbers from 1 to 20000 or whatever in your data.

Click any cell in the data and Insert > Pivot Table > Enter to start the process.

Second, Put your Index field in the Row Labels area, and the field or fields you need the Median for in the Values area.

Set the Value Field Settings to Sum and turn OFF the Column Totals.

Now open the Slicer tool and check off any fields you need to use as qualifiers in the Slicer Boxes.

Most of you probably know where I am going now and wonder why you have never thought of it yourself

If you use the slicers, you will see that in the Value columns, only the numbers that corrilate with the combination of Slicers you select will appear in the pivot tables. This is due to your Index Number, which will only allow one number of the selected Slicer Fields to appear in each row, and all the rows that contain the sliced fields will be in the list.

Now in another part of the sheet (not in the same rows or you will throw off the calculations) put the formula for the Median of the numbers the rows you need. (If the Cost of Goods is in Column B, then the formula would be MEDIAN(B:B), but it can't be in Column B because that would be a circular reference, and it would throw off your Median). That's why you need to turn off the Column Totals.

Try it, you'll love it!

Van!!