Which is the correct way to calculate the average percentage?

[Bear]

Dear all

May I know what is the correct way to calculate the average percentage? Method A or Method B? If both are correct, does it depend on whether I want a high or low percentage?

	Sales​
	2023	2022	2023 vs 2022
Product A	$ 200	$ 168	19%
Product B	$ 659	$ 250	164%
Product C	$ 320	$ 450	-29%
Method A			51%
Method B	$ 1,179	$ 868	36%	<= SUM(B3:B5)/SUM(C3:C5)-1

 

[ETAF]

you cannot average averages

1/10 = 10%
1/100 = 1%
average = 5.5%

BUT
2/110
=1.8%

you can get the average by product OK

BUT the overall total needs to be the total of all the products and compared

anyway - thats what i was always taught, in Maths & statistics at uni
and you will see lots of articles about this

Why you should be careful when averaging percentages — Roberto Reif

A common mistake I often observe in data analysis is the averaging of percentages. To explain this concept let’s examine a simple example. Suppose we conduct a survey on whether people like or do not like chocolate. We discover that 90% of children like chocolate; however, only

www.robertoreif.com

How To Calculate Averages Of Percentages - Sciencing

A percentage is a proportion (or ratio) that states the number of parts or items per 100. For example, if 37 percent of the pencils in a box of 100 pencils are red, this means 37 of the pencils are red. Percentages for more than one category can be averaged, but it can be a little tricky. In...

sciencing.com

 

[Bear]

you cannot average averages

1/10 = 10%
1/100 = 1%
average = 5.5%

BUT
2/110
=1.8%

you can get the average by product OK

BUT the overall total needs to be the total of all the products and compared

anyway - thats what i was always taught, in Maths & statistics at uni
and you will see lots of articles about this

Why you should be careful when averaging percentages — Roberto Reif

A common mistake I often observe in data analysis is the averaging of percentages. To explain this concept let’s examine a simple example. Suppose we conduct a survey on whether people like or do not like chocolate. We discover that 90% of children like chocolate; however, only

www.robertoreif.com

How To Calculate Averages Of Percentages - Sciencing

A percentage is a proportion (or ratio) that states the number of parts or items per 100. For example, if 37 percent of the pencils in a box of 100 pencils are red, this means 37 of the pencils are red. Percentages for more than one category can be averaged, but it can be a little tricky. In...

sciencing.com

Thanks ETAF
I asked the above questions with a couple of colleagues, but no one can give me concrete answer. Thanks for clarifying on this.

 

[pecoflyer]

BUT
2/110

Where does 2/110 come from ? Averaging averages would be 11/100 or 0.11 in this example

 

[ETAF]

1 out of 10
1 out of 100

2 out of 110

averaging the averages in excel
10% and 1% gives 5.5%

 

[joeu2004]

May I know what is the correct way to calculate the average percentage?

It really depends on what average that we want to calculate.



Formulas:
D3: =B3/C3-1
E3: =C3/$C$8
D6: AVERAGE(D3:D5)
D8: =B8/C8-1
D9: =SUMPRODUCT(D3:D5,C3:C5)/SUM(C3:C5)
D10: =SUMPRODUCT(D3:D5, E3:E5)


If we want to calculate the average product percentage change, the simple average (D6) is indeed the correct method.

But if we want to calculate the total percentage change (e.g. the portfolio change), there are 3 methods, depending on our data.

Method 1: If we have the two totals (B8 and C8), total percentage change is calculated the same way that each product percentage change is calculated, namely the formula in D8.

Method 2: If we only have each product percentage change (D3:D5) and the original values of each product (C3:C5), the total percentage change is the weighted average (D9), which is the sum of the products of each product percentage change weighted by (times) the percentage of the product's contribution to the original total.

Method 3: If we only have each product percentage change (D3:D5) and its percentage contribution to the original total (E3:E5), again the total percentage change is the weighted average (D10).

Note that the weight denominator is the original total (2022), not the new total (2023).

I could demonstrate that algebraically.

But here, it should be sufficient just to demonstrate that D9 and D10 equal to D8, within the limitations of binary floating-point arithmetic.