Technically, the end result isn’t a PivotTable, but you can use the PivotTable feature to set up your array more easily:ġ. The steps demonstrated will apply to Excel 2010 and later. As with all workarounds, it may not be elegant, but it can get you started. Here is the first of two “workaround” solutions to get a median calculation out of your PivotTable information. But what if we want the median donation for each state?
If we want to understand our donor giving patterns by state, we would naturally turn to a PivotTable and get something like we see in the first image. To follow using our example below, download Calculate Median in PivotTables.xlsx Images in this article were taken using Excel 2013 on the Windows 7 OS.
This number more realistically reveals our donor giving patterns. The median calculation shows us that most of our donations are around $50 with half giving more, and half giving less. The average donation for the year is $1380.50, but that does not accurately show us how most of our donors give. Most of the donations are small amounts, a handful are more significant, and one or two are very large. Our example shows donations collected by a regional charity. However, averages aren’t always the best way to understand your data. Meaning, there isn’t a simple way to change the Value Field Settings to calculate “Median”. The short answer is “Excel doesn’t do that”. By Tepring Crocker Categories: Excel®, PivotTables Tags: excel pivot table medianĪ frequent question that comes up when working with Excel PivotTables is how to calculate median using the table’s filters and analysis.
0 kommentar(er)
