weighted average of Dogs by Country

[jamiguel77]

hi all, how to calculate: weighted average
The table below shows the population by country that owns dogs, (and how many)
with this data:

Libro5
	A	B	C	D
10		No Person	Qty Dogs
11	Argentina	1,000	60,000
12	USA	10	700
13	Mexico	5,000	90,000
14		6,010	150,700
15
16		Average:	25.07487521
17		Weighted average:
18
Hoja3

Cell Formulas
Range	Formula
B14:C14	B14	=SUM(B11:B13)
C16	C16	=C14/B14

the result would be on: c17
An explanation of the weighted average?


thanks

 

[SanjayGMusafir]

is this what you are looking for? Check and revert -

Book2
	A	B	C
10		No Person	Qty Dogs
11	Argentina	1000	60000
12	USA	10	700
13	Mexico	5000	90000
14		6010	150700
15
16		Average:	25.07487521
17		Weighted average:	26.0748752
Sheet1

Cell Formulas
Range	Formula
B14:C14	B14	=SUM(B11:B13)
C16	C16	=C14/B14
C17	C17	=SUMPRODUCT(B11:C13)/B14

 

[SanjayGMusafir]

An explanation of the weighted average?

Weighted Average takes into account variable quantities across data spread...

So in your data set we multiply figures in column B with Column C and then average it with total number of persons

 

[jamiguel77]

Thanks, if possible, why is different cell: c16 and c17 wich is the correct averade, and why.
Thanks

 

[SanjayGMusafir]

Thanks, if possible, why is different cell: c16 and c17 wich is the correct averade

Weighted average is a better average as it takes into account quantity variation across data spread.

 

[jamiguel77]

Thanks

 

[SanjayGMusafir]

Glad to help you.