Hello!
I am wanting to formulas to get the Average, MIN and MAX of the values generated for the Grand Total row of a pivot table.
I don't want to add extra columns to display the information.
By selecting from the slicer, both the row count and column count vary. How can I reference the range of values that appear in the Grand Total row so that I can perform AVERAGE() on this range?
I can find the starting and ending cells of the Grand Total row but when I use these in an AVERAGE() the value is wrong (different than what appears at the bottom of Excel when I just highlight all the values.
Starting cell:
=ADDRESS(MATCH("Grand Total";A:A;0);2)
Ending cell:
=ADDRESS(MATCH("Grand Total";A:A;0);(COUNTIF(B7:N7;"<>")+1))
(not pretty but looks correct for any slicer choice)
BIG thanks in advance for any help!!!
Bookmarks