Forum Discussion
Take in account new lines in a pivot table
Hello dear all, I have made a pivot table with two columns. I use (in a different cell, outside the pivot tabl) the "Average" function to calculate the average value of the results that appear in...
Pierrick_Guinot Yes, you can replace the range you're averaging with this:
=OFFSET(B5,0,0,COUNTA(B:B)-1,1)Where B4 is the header in the pivot table, and B5 is the first value of the range you will average. This formula will count how many rows in column B are populated with data. The "-1" directly after the Counta function accounts for the header row being populated. If you have filters or other data that is in column B, then you will need to update the -1 to account for them so the range doesn't expand below the pivot table. e.g. if you have two filters you would replace the -1 with -3.
