Using dynamic table's average for a chart

[MrsLisa]

Hello. I need to calculate a dynamic table's average outside of the table to add back inside the same table (in a column) to build vertical average line in a bar average line chart. The table average will fluctuate as attributes are selected with sliders. I do know I can average on the bottom, but I need to refer to the average inside table so I have to calculate outside of the table as well. I've search but I cannot figure out how to calculate the average dynamic tables. I uploaded a picture to clarify. I uploaded the file. Thank you in advance, your help is greatly appreciated.

 

[Chihiro]

You can use =SUBTOTAL(1,Table[Column]).

Which will ignore filtered values.

 

[MrsLisa]

Thank you Chihiro but I don't how that will provide an average. Sorry I'm just started teaching myself tables etc. If you or anyone gets a moment to see if I'm attacking this incorrectly. I was able to upload the worksheet to my original post.

 

[Chihiro]

Did you at least try?

SUBTOTAL's first argument is calculation type.
1 = AVERAGE
2 = COUNT
3 = COUNTA
... etc.

See attached. Filter the table and see how Avg in G1 changes.

 

[MrsLisa]

Did you at least try?

SUBTOTAL's first argument is calculation type.
1 = AVERAGE
2 = COUNT
3 = COUNTA
... etc.

See attached. Filter the table and see how Avg in G1 changes.

Thank you Chihiro. This is the step in the right direction I needed. I searched and search under index but I never saw the subtotal. Your posted sounded too good to be true. lol I will study argument this weekend. Have a great day.

 

[Baama2]

My Dear Mr. Excel,


I am baffled by how a chart was created. I went to a job interview and I was asked to create a chart in Excel. I tried everything I know but I could not create the chart. This is not the first time I have tried this chart. Please see attached.

I have another interview next week, and I am afraid this chart will be waiting for me. It would very much appreciated if you could help.


Please see attached.



Thanks you in advance


Sheik Barry

 

[Chihiro]

I'd suggest you post in new thread as this is totally unrelated to original question.

As for your question, you have 2 series originating from different source. That's why it won't group properly.

See attached samples. Chart9 is done from original data.

Added sample PivotChart as well (which is useful when you want to group/summarize data by month/year).