Simple or Weighted Average?

[Jay]

Hi,

My query is less about the technical side of Excel & more about which
technique to use.

I have a database which tracks the monthly depreciation in car values,
for cars of various ages (i.e. 1,2,3 years old etc.).

I want an overall average monthly depreciation, so I've extracted the
values for each month & compared them to the next month's values for the
same cars. However, I have sample three different ages (1,2 & 3 years).

To get the average monthly depreciation for each age I average all the
valus for each month and then use these in the following:

=(Average(BaseMonth)/Average(NextMonth)-1)*100

So I have (for example):

Avg BaseMonth Avg NextMonth % Depr
1 yr-old 13689 10235 -25.2
2 yr-old 9856 9125 -7.4
3 yr-old 4695 2986 -36.4

My question is, to get an overall Average, do I Average the three Base &
Next numbers again & use the previous formula, or do I simple average
the 3 percentages?

I want the overall Average as a (very basic) guide to overall
depreciation so it won't nexessarily be the same cars or the same number
of cars in the future - that's why I was wondering whether a simple
average (of just the percentages) would be more appropriate.

If anyone could advise me I'd be most grateful,

Many thanks,

Jason

 

[Bernard Liengme]

Try this:
Set A: {1,2,3} average is 2
Set B: {2,4,6,8,10} average is 6
Set C: {3,6,9,12,15,18, 21} average is 12
The average of these three averages is 6.666 (if it means anything!)
The average the union of these sets {1,2,3,2,4,6,8,10, 3,6,9,12,15,18, 21}
is 8
The weighted average ((2*3) + (6*5) + (12*7) )/(3+5+7) = 8

I will leave others to comment on your use of percentages changes. Why not
just changes?
best wishes

 

[KC Rippstein]

Averaging averages is usually not an appropriate mathematical process, as
pointed out here by Bernard. It is usually a meaningless number. You
usually want to defer to your original dataset values and calc your own
"overall" average.

Even in your example below, for each category, you give overall average
value this month versus next month and overall average depreciation
percentage. You cannot then use these numbers to serve any further purpose.
An average of the precentages gives you -23.0%;
sum(NextMonthAverages) / sum(ThisMonthAverages) - 1 gives you -20.9%.
Both answers are off base.

You need to go reference your original dataset and do the following:
sum(all NextMonthValues) / sum(all ThisMonthValues) - 1
This will give a true picture of your overall depreciation for all cars in
all categories in the current inventory, which will be -22.6% in your
example.