Blog

How can you add an AVERAGE calculation to a pivot table column? Pivot tables are a great tool for summarizing data and include many helpful features, including the ability to calculate averages on rows of data. But what if you want to include an average on a column of pivot table values? Or what if you want to use functions not natively supported by pivot tables, such as median or mode?

Adding a median or other calculation to a pivot table row

First, let’s address the easier challenge of adding a median or other calculation not supported natively to a pivot table row. If you haven’t already, add your data to the Data Model, then using Power Pivot add a calculated column or a function with the necessary calculation. Basing your pivot table on the Data Model, you can now include that column or function into your pivot table.

Adding a mean (average), median, or other calculation to a pivot table column

Now let’s look at how to add one of these calculations to a pivot table column. Since this can’t be done natively within a pivot table, nor will it produce the desired result if done on a column in the Data Model, we need to add the desired calculation outside of the pivot table yet based on the data within the pivot table. While calculating an average on a fixed range of data is quite straightforward, the challenge here comes because the number of rows in the pivot table may constantly be changing. Let’s look at an example to see how to work around this.

The problem

A client had a process that went through various stages and wanted to calculate the average time a job spent in each stage. Dates were available for each stage, and in Power Pivot we were able to calculate the number of days between each transition from one stage to the next. But how to get an average of these values in the resulting pivot table.

Building a solution

Here is the approach I settled on to build a formula to calculate the average or mean in such a way that it will be dynamic and adjust as the pivot table grows or shrinks. The screen shot below shows a pivot table based on some sample data we will work with. As set up presently, the data goes from row 8 to row 59.

Let’s add a manual formula as a check amout to test against. Remember that the syntax for the average formula is:

=AVERAGE(number 1, [number2], …) or =AVERAGE(range)

Thus, we would manually write it like this

Now let’s see how we can modify this formula to make it dynamic, changing with the pivot table. This will require three things as follows: 1) a fixed starting point for the range, which will always be cell D8, 2) a way to determine the last row of the pivot table, and 3) a way to turn that into the end of the range.

A bit of searching on Google provided a number of possible ways to determine the last row in the pivot table. The one I found useful for my purpose was (and if I could find the site again, I would credit the woman who provided it):

=MATCH(REPT(“z”,50),$B:$B)

It works by trying to match a string of 50 z’s to text in row B (or whatever column you specify in the pivot table) and giving you the last row of text. Note that the column must contain text, as this won’t work with numbers or dates, and should be a column that ends at the last row of the pivot table. This now gives us the row number of the last row of the pivot table, as follows:

Now we want to turn this into the address of the end of our range. So far, we have the row number of the address. To get the column, we can use the COLUMNS function like this:

=COLUMN(D2)

This will return the value 4, as column D is the fourth column.

To turn this into and address, we feed this information into the ADDRESS function, whose syntax is:

ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])

Our formula becomes:

=ADDRESS(MATCH(REPT(“z”,50),$B:$B),COLUMNS(D2))

Now we can combine all of this into one formula, with one additional step required. To get the range in our AVERAGE formula to recognize this as the address of the end of the range, we have to use the INDIRECT function to tell the formula to interpret the result of this formula as the end of the range, as follows:

=AVERAGE(D$8:INDIRECT(ADDRESS(MATCH(REPT(“z”,50),$B:$B),COLUMN(D2))))

Here is the result, with another formula below it to calculate the median (just substituting MEDIAN for AVERAGE):

Finally, let’s test how dynamic this is by filtering the pivot table to reduce the data to only go to row 21. I have adjusted the formula in cell E2 to manually calculate this as a check on our formula.

We now have a dynamic formula that can be copied to other columns as needed and which will adjust to the pivot table’s changing size, giving the ability to create formulas external to the pivot table that we couldn’t come up with a way to do within the table.

And if someone reading this comes up with a way to do this within the pivot table or the Data Model (as there are usually at least 4 ways to do anything in Excel), I would be interested in hearing from you how you accomplished it.

Final Thoughts

This process is also a good demonstration of how we can build formulas to accomplish what is needed by starting from a simple scenario and continuing to add pieces to get us to where we need to end up at.

Sometimes we make this more complicated in our own minds than it needs to be. I remember reading an interesting article (and if I could find it again, I would happily provide the link) in which the author highlighted that there are only a few basic types of data in Excel, numbers, dates, text strings, ranges, and the like. If we keep in mind what type of data each function requires for arguments and what type it returns, then we can select a function to use inside another function that will provide what it needs to do its role.

For example, the AVERAGE function requires a range and returns a number. The ADDRESS function requires two numbers and returns a range of one cell address, thus it can be used inside the AVERAGE function. Keeping in mind what each function needs and can provide helps us use them effectively within other functions.

I hope this example provides both a practical solution to the problem of using functions on a pivot table that aren’t supported natively and a practical example of building more complex formulas when needed.

And please reach out to me if I can help you find a solution to your unique reporting need.