Average %age Calculations

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jay - 27 Jul 2006 23:47 GMT

This query is as much about mathematical method as it is Excel.

A day-to-day task is to review sales data against expected values. So,
let's say I have 2 columns i.e.

Sold Price Expected Value
5000 4500
3000 2800
4500 3850
3850 4375

With a few thousand rows.

So, my question is - what's the best way of calculating the Average % of
Expected Value realised across ALL sales? Each of the sales above
individually works out as:

5000 4500 111.11%
3000 2800 107.14%
4500 3850 116.88%
3850 4375 88.00%

So, the two methods I can see of calculating the OVERALL Average % are:

1) Average the % data above-gives Avg% of Expected Values over ALL sales

2) SUM or AVERAGE both of the original columns & calculate the % i.e.

5000 4500
3000 2800
4500 3850
3850 4375
------------
16350 15525 105.31%

So, I have 2 questions:

A) Which method gives the most accurate Result.
B) Why can the Final Avg % differ by up to 10% between both methods?

Thanks for taking the time to read this, but we just can't figure out
why the 2 methods can give such varied results. (a little variance would
be expected)

Any help greatly appreciated.

Jay

Reply to this Message

Bob Phillips - 28 Jul 2006 00:12 GMT

The reason is that when you average averages, you treat each average with
the same weight, regardless of the size of the data that each
used/averaged.

For instance, 5 and 4 gives 125%, 22 and 20 gives 110%. If you average these
you are effectively taking the average of 1.25 and 1.1, which comes out at
1.18. If you add them before averaging, the data sizes is taken into
account, so you get 27 and 24, which comes to 1.13. It is much smaller,
because the data that gives a smaller average is much greater in size, so it
is a bigger factor in the overall average.

Averaging averages is not a good idea.

BTW 10% is nothing, it can be a s big as you can imagine, a simple example

10 4 250%
21 20 105%
31 24 --- 178%
129%

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> This query is as much about mathematical method as it is Excel.
>
[quoted text clipped - 43 lines]
>
> Jay

Reply to this Message

Jay - 28 Jul 2006 07:41 GMT

> The reason is that when you average averages, you treat each average with
> the same weight, regardless of the size of the data that each
[quoted text clipped - 15 lines]
> 31 24 --- 178%
> 129%

Thanks for the reply Bob. What I am trying to do is determine the
overall Average accuracy of the expected value, which is a forecasted
value my company caclulates. So, taking all actual sold prices into
consideration, what is the average % of thge forecast achieved
(obviously the nearer 100% the better.)

SO you'd agree that method 1 is better?

Oh, and is method 2 really averaging averages? because it's an average
of the % relationship between sold_price & forecast - Does that stil
count as averaging averages because it's a 2-step calculation?

Many thanks

Jay

Reply to this Message

Bob Phillips - 28 Jul 2006 07:54 GMT

No, I am saying that I think that method 2 is better, add everything before
averaging them (in fact it is not average, but just a straight percentage
calculation). IMO, the first method has no meaning whatsoever.

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> > The reason is that when you average averages, you treat each average with
> > the same weight, regardless of the size of the data that each
[quoted text clipped - 31 lines]
>
> Jay

Reply to this Message

joeu2004@hotmail.com - 28 Jul 2006 10:18 GMT

> So, my question is - what's the best way of calculating the Average % of
> Expected Value realised across ALL sales?

>From your example, I think you are really asking: what is the "best"

way to calculate the "average ratio" of actual v. expected price.

> Each of the sales above individually works out as:
> 5000 4500 111.11%
[quoted text clipped - 4 lines]
> So, the two methods I can see of calculating the OVERALL Average % are:
> 1) Average the % data above-gives Avg% of Expected Values over ALL sales

I assume you mean AVERAGE(111.11%,...,88.00%)

> 2) SUM or AVERAGE both of the original columns & calculate the %

I assume you mean SUM(5000,...,3850) / SUM(4500,...,4375)

#1 is called the simple average; #2 is called the weighted average.
Although the weighted average is often the better choice, there are
times when the simple average might apply. See the article at
http://mathforum.org/library/drmath/view/64391.html for one
perspective. Although the article is not well-written, IMHO, at least
it demonstrates that the "right" answer is not as cut-and-dried as some
people might have you believe.

For example, consider comparing the "average ratio" above with the
"average ratio" for the following results:

Actual    Expected    Ratio
5000    4375    114.29%
3000    3850    77.92%
4500    2800    160.71%
3850    4500    85.56%

Since each column has the same numbers, simply in a different order,
method #2 would result in the same "average ratio", whereas method #1
would reflect a difference.

Which one is "right" depends on whether you want to reflect the
"average of the total"
(method #2) or the "average individual result" (aka "typical result";
method #1).

----- complete original article -----

> This query is as much about mathematical method as it is Excel.
>
[quoted text clipped - 43 lines]
>
> Jay

Reply to this Message