0

I am trying to find a group of functions that allows me to calculate the avarge of a dynamic range for a pivot table. I know I have to use Offset & Counta however even when I change the data it only picks up the amount that I initially inputed.

For Example:

Pivot Table

I have two sets of pivot table data. One with only 4 weeks and the grand total and the other with 5 weeks and the grand total. I used the function for data 1 (4 weeks) = Average(Offset, C3, 0, 0, (Counta (C3)), Counta (C3:F3))).

On the left (A3:A5) I am given the average. If I were to add a week 5 as seen in the second data It still only picks up C10:F10 and not E10 (week 5).

I am trying to find a function that allows me to pick up the average of the weeks ** without ** the grandtotal in it. So if I were to add 10 weeks the average were to automaticlly pick up the range C10:C20. and not the grandtotal.

Brianna Ford
  • 1
  • Is there a reason you don't use the average built into the Pivot tables? – cybernetic.nomad Feb 21 '23 at 18:11
  • Because we are doing a summary report, the average data will go into another excel sheet. So I aim to copy and past the list of averages and paste them into the summary report. – Brianna Ford Feb 21 '23 at 18:31

0 Answers0