Pivot Table Averaging Formula Question

Hi, I am doing a profitability analysis in excel with about 17 columns and 450 rows so far. On each column, I have a pivot table placed at the top so I can filter by specific fields. On sheet 2, I have made simple average formulas using each column in sheet 1 so I can reference it separately. What I would like to do is be able to go to sheet 1 and filter the information using the pivot tables and in sheet 2, have the new averages for the specific fields I chose in the filter. Currently, it just shows the same averages for all of the rows no matter what filters I use. Hopefully this makes sense. I am an Excel amateur and bad at explaining things.

So basically, I just need some help making a formula that will take the average of the data shown on the first sheet after being filtered (not the entire data set). Thank you!!!

Ok, I attached a very simplified version of what I am trying to accomplish. So in the first sheet I have all of the variables of each order with pivot tables in each column so I can filter the results. In the second sheet, I have formulas to average some of the columns.

Now,for example, lets say I filter the "customer" column so that only Dan is showing. I want the averages in the second sheet to change to the averages of just Dan (or what is currently being shown after the filter is used).

Hopefully this helps.

Thanks for the reply.

MockSheet.xlsx

That is perfect, thank you!