Take in account new lines in a pivot table

[Pierrick_Guinot]

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 the second column of my pivot table.

The problem is that every time I enter new data in my Excel file, new lines appear in my pivot table, and the "average" function does not take in account these new lines.

Do yo guys know if there is a function that allows me to automatically update the range of my "average" function, in order to take in account the new lines that appear in my pivot table ?

Thanks a lot in advance.

[#2]

@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.  

[#3]

Thanks a lot @DexterG_III for your answer, I will try that ASAP.

Regards,