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

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%


--
HTH

Bob Phillips

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

"Jay" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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

Bob Phillips wrote:
> 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%
>
>
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

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.

--
HTH

Bob Phillips

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

"Jay" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Bob Phillips wrote:
> > 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%
> >
> >
> 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

Jay wrote:
> 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%
> 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

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 -----

Jay wrote:
> 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