+ Reply to Thread
Results 1 to 2 of 2

Get Average, MAX and MIN values form the values in the Grand Total row of a pivot table

  1. #1
    Registered User
    Join Date
    01-08-2022
    Location
    Norway
    MS-Off Ver
    o365
    Posts
    5

    Question Get Average, MAX and MIN values form the values in the Grand Total row of a pivot table

    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!!!
    Attached Files Attached Files
    Last edited by Agrajag909; 01-09-2022 at 11:52 AM.

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Get Average, MAX and MIN values form the values in the Grand Total row of a pivot tabl

    if you've nothing else on your tab, just average the entire row

    =AVERAGE(INDEX(A:IV;MATCH("Grand Total";A:A;0);0))

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 11
    Last Post: 09-27-2019, 08:00 AM
  2. Replies: 5
    Last Post: 11-13-2018, 05:28 PM
  3. Pivot Table Grand Total Average
    By temexcel in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-16-2014, 02:32 PM
  4. Pivot Table, Need Grand Total as an average
    By children in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 02-13-2014, 01:37 PM
  5. [SOLVED] Is it possible to have both Grand Total and Grand Average in a Pivot Table?
    By ANS in forum Excel Charting & Pivots
    Replies: 15
    Last Post: 10-16-2012, 05:15 AM
  6. Pivot Table Grand Total on Average
    By kmlloyd in forum Excel General
    Replies: 0
    Last Post: 03-16-2011, 12:21 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1