Wrong Excel Average Formula

[mani0525]

I have a problem with my Excel. I have a statistic with two filters that allowed for ratings of 1 to 5 (People >25 and People 25).

Out all those comments, I want to have the average.

According to my Pivot, the Average for People Aged 25 and Under is 2,854.

Pivot Single Output: People >25: 3,169 Pivot: Single Output: People 25: 2,700

In my opinion, the correct number should be 2,935 rather than 2,854. (3,169+2,700)/2

My Excel format is Standard, but I've also tested Numbers.

Any suggestions?

I use Office 365.

I made an effort to compute it manually as well as using a standard Excel formula.

 

[GraH]

Hi, It is not clear to me what average you calculate?
It's not the age, then it's the rating. What does this rating represent? Is it logic to calculate the average of such rating?
Maybe you can share a sample.

Try AVERAGEIFS, I expect it to return the same as the pivot.
I now suspect you do an average of an average because of: (3,169+2,700)/2

 

[RoryA]

In my opinion, the correct number should be 2,935 rather than 2,854. (3,169+2,700)/2

Only if there are exactly the same number of people in each of the two categories.

 

[Alex Blakenburg]

This comes up quite a lot and Rory has answered on a number of occassions.
Jon Acampora discusses this exact issue here:

Pivot Table Average of Averages in Grand Total Row - Excel Campus

Find out how the Grand Total calulates the average of the rows above while the status bar can sometimes show the average of an average.

www.excelcampus.com