Average a column, exlcude a row in a dynamic pivot table

Hi all,

So I'm an amateur with Excel but loving using pivot tables for work.

What I am trying to do is this: Average a large set of % numbers in one column, and for the formula to ignore one row in particular called "grand total"


using =AVERAGE(b:b) works to average the column but it also includes the grand total which obviously throws the calculation off.

Because the data is in a PivotTable with constantly changing data, the "Grand Total" row will be in a different row number every day, so I want the formula to ignore the grand total line.

Thanks! really appreciate the help.

Can you do this =(SUM(B:B)/2)/(COUNT(B:B)-1)

Originally Posted by Motu040602

Can you do this =(SUM(B:B)/2)/(COUNT(B:B)-1)

Thank you for the reply. Unfortunately that didn't work. Maybe because the column in question is a %?


For instance, on other columns with the same issue I was able to use a large number to exclude the grand total. Example: =AVERAGEIF(B:B, "<999999")

Thanks Haseeb! Took a little tweaking but it worked :D

Solved it better just now using the GETPIVOTDATA.

Thanks for the replies! Looking forward to using this forum more often :D