Median in Pivot Table

vicramirezb

New Member
Joined
Feb 1, 2003
Messages
1
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
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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.
 
Upvote 0
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
 
Upvote 0
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!!
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,585
Messages
6,179,705
Members
452,939
Latest member
WCrawford

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top