• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Average of averages

anonymous8472

anonymous8472

Member
How is this done?


I need the average of the averages of 2004-Q1:2004-Q4'!G3, 2005-Q1:2005-Q4'!G3, 2006-Q1:2006-Q4'!G3, etc etc etc.....
 
Hi, xenormorph8472!


If it's for the same file of your previous post, just replace MAX function for AVG and cell C3 by the expression you need... to construct. Because in YYYY-QQ sheets the only "average" is in column H and contains invalid data for mathematical operations.


If not, consider uploading the related sample file.


Regards!
 
Finding the average of averages is mathematically incorrect. It actually does not find the real average of values. To find the real average you must take the total of item1 divided by total item 2. Just and FYI.


Check out google if you don't believe me. Average of Averages gives incorrect values.
 
@Montrey

Hi!

Maybe the user is interested in studying the media behavior thru the Q's, its StdDev, who knows. I wouldn't do it, but...

BTW it's very useful your comment, just in case he isn't aware of that fact.

Regards!
 
I'm not sure I agree with Montrey's Average statement

"To find the real average you must take the total of item1 divided by total item 2"


The average of Averages is also incorrect.


The arithmetic Average of multiple groups is the Sum of all Groups / the Count of Items in all groups. This removes all weighting bias due to differences in sample size of each group.


If you are using a weighted or Geometric Average it is the sum of the (Items * weighting factor) / sum of the weightig factor.
 
@Hui

Hi!

Long ago I've worked on a project where something alike happened. The corporate HQ asked for quarterly reports and stats, and comparisons against last Q, same Q previous year, running Qs current year, up to here everything fine, but there was couple of sub-reports that compared quarterly info against the average of the last 12/24/36/48/60 months in Q (4/8/...).

With the figure that represented quantities, amounts, cases, no problem... but some of them were averages! What for? I still don't know, but I was sent back summary reports made at HQ where they handled Q average of let's say selling (100+150+120=370/3=123,33, 80/100+120=300/3=100, and so on) and they calculate an averages of averages (123,33+100=223,33/2=116,67), they build bells with them, calculated StdDev, and a lot of stuff... What for? Somebody half-drunk half-sleepless might have asked for that.

I don't know if xenomorph8472's facing the same scenario, if so, well, we can ask him/her why and how for all gods sake is used that info of the average of averages (which I agree that it's a overall false average); if not, he'd have noticed Montrey advise...


Regards!
 
Ah...thanks for all the input, would have been much more helpful had I attached the file...in answer to SIRJB, it is the same sheet.....


I need to figure average ANNUAL net absorption, (the worksheets only reflect quarterly net absorption) which would be the average of the averages of a four quarter range would it not?


https://docs.google.com/open?id=0B15c6eYowyduUllwQ2c0WmRVZGM
 
Since all quarters I would assume have the same weight. The real average would be to total each quarter then divide by 4. Not to Average each quarter than average those averages.


SirJb7, Chime in with your opinion on this one!
 
=SUM('2004-Q1:2004-Q4'!G3,'2005-Q1:2005-Q4'!G3, '2006-Q1:2006-Q4'!G3,'2007-Q1:2007-Q4'!G3)/4


Put that in N3 on the summary tab and drag down. If you want to add more years, just copy a small part of the above formula and replace the year with years you want to add. then just add 1 to the 4 for ever year you add. For example. If I wanted 5 years instead of 4 which is stated above it would look like this.


=SUM('2004-Q1:2004-Q4'!G3,'2005-Q1:2005-Q4'!G3,'2006-Q1:2006-Q4'!G3,'2007-Q1:2007-Q4'!G3,'2008-Q1:2008-Q4'!G3)/5
 
Works! That is brilliant...you guys are life-savers....helping with my thinking process when it come to excel as well. I've been banging my head against the wall for 3 months on this!


Really appreciate it!
 
Glad it worked. It is adding Net absorption of each quarter for each year then divides by the number of years. the formula assumes the data are in the same cells of each sheet.
 
Hi ,


Just one point ; the AVERAGE function does not consider the cells which are blank ; is this OK with you ? If not , the average value calculated using the AVERAGE function is itself wrong.


Narayan
 
Blank cells are only in the first year....I may exclude 2004 and see what happens.


@montrey, is the SUM function interchangeable with, say MAX or MIN?
 
Hi ,


If you replace the blanks with 0 , the calculated average value will change ; this means that excluding the blank cells will not change the calculated value , since that is what the AVERAGE function is doing.


2007-Q2 is also blank.


As it stands , the average annual value is just the average quarterly value multiplied by 4.


Narayan
 
@xenomorph


You can use Max and Min, but it will find the max/min of the quarters not the year.


You would have to total each year then use max/min on the those totals for the years.


Narayank is correct in that Years with 0 values will skew your data because of the extreme value differences between years. So you may want to remove 2004 if it has no data. Make sure to also change the total # of years divided by. I.e. 4 to 3, or 5 to 4
 
@Montrey

Hi!

Let me think for a while... Yeap, I've got it.

a-quarter-drunk three-quarters-sleepless?

:)

Regards!
 
Yes precisley! Un-even data is extremely frustrating. grr. I propose a solution to all such problems! Everyone! please stop the practice of using a different sheet for different data sets! Put all data on the same sheet!
 
Hi, xenomorph8472!

Tried with this?

=MAX(SUM('2005-Q1:2005-Q4'!G3),SUM('2006-Q1:2006-Q4'!g3))

Regards!
 
You must log in or register to reply here.
Back
Top