Calculation of an Average

  • Thread starter Thread starter mate
  • Start date Start date
M

mate

I have the following data setup:

FREQUENCY MARKUP
..2558 28%
..1794 25%


there is more data but i won't bother listing it. the
frequency adds up to 1 of course. Now how would i
calculate the average markup of all orders? (331 in all)
Any help is greatly appreciated as always. thanks, mate
 
I believe the standard way to find an average is to sum the markups (or
whatever) and divide by the count of markups (or whatever).
=SUM(Markups)/COUNTA(Markups)
 
With your frequency in A1:A331 and Markup in B1:B331

In any other cell:-

=SUMPRODUCT(A1:A331*B1:B331) formatted as a %
 
The general formula for a weighted average is

=SUMPRODUCT(Data, Weights)/SUM(Weights)

In your case the data is the markup, and the weights are the frequencies.

If frequency is in A1:A331 and markup in B1:B331, since you say the frequencies sum to 1, you
can eliminate the division:

=SUMPRODUCT(A1:A331,B1:B331)
 
If I understand clearly that your frequencies do add up to 1 then to find the weighted average sum the product of the frequency and the markup percentage.

To calculate a simple average of the markups then sum the markups and divide by the number of rows in your list. this does not then take any account of frequencies.

Best of luck.
 
Back
Top