Weighted Average Help (Take 2)

[gregz1234]

Hello,


I'm attempting to calculate a single weighted average number for international transit times, based on the number of shipments going to the international destinations.


Data Sample:

Transit Days Per Ship	Transit Days	Country	Shipment Total
20.53	1683.47	China	82
7.42	452.35	Macau	61
11.97	454.98	Brazil	38
4.27	153.81	Canada	35
5.32	186.09	HK	35
5.80	191.25	JP	33
6.41	173	Korea	27

<tbody>
</tbody>

I had posed this question previously and had gotten the suggestion try excel's harmonic mean function based just on the transit days per ship numbers. The result was not favorable for my boss and was told we need to factor in shipment totals regardless.

I had attempted to do the weighted average formula, with the shipments as the 'weight' however that resulted the same as if I had just added up the total transit days and divided them by the shipment total.

Any help would be greatly appreciated, thank you all in advance for your time~!!

-Greg

 

[AhoyNC]

You should get the same answer.
In your case if shipment total (to a country) is the weight and you multiply it times the transit days it is going to equal the total transit days to that country. So, either way would give you the same weighted average.
One reason for using a harmonic mean would be if you had extreme values and you want to smooth things out where this values don't skew the data as much. I don't think that's what you want in this case. I'm assuming that in your data since China had more shipments you want it to have a higher weight.

In your example I get 10.595 days as the weighted average. You will then notice I added an example 2 where I keep the shipment grand total the same, but increased the number of shipments to China and lowered a few others which changed the weighted average to 11.358 which weights it more towards the China shipments.
Hopefully this helps some, but I don't know what your boss wants or how he plans to use the data.

Excel Workbook
	A	B	C	D	E
1	Transit Days Per Ship	Transit Days	Country	Shipment Total
2	20.53	1683.47	China	82
3	7.42	452.35	Macau	61
4	11.97	454.98	Brazil	38
5	4.27	153.81	Canada	35
6	5.32	186.09	HK	35
7	5.8	191.25	JP	33
8	6.41	173	Korea	27
9		3294.95		311
10
11	Weighted Average	10.595
12
13	Example 2
14	Transit Days Per Ship	Transit Days	Country	Shipment Total
15	20.53	2053	China	100
16	7.42	333.9	Macau	45
17	11.97	454.86	Brazil	38
18	4.27	149.45	Canada	35
19	5.32	170.24	HK	32
20	5.8	191.4	JP	33
21	6.41	179.48	Korea	28
22		3532.33		311
23
24	Weighted Average	11.358
25
Sheet

 

[gregz1234]

Thanks! Ok, that helps to confirm it a little better for me. He more or less just wants an single international transit average number. (It's a little odd I know). Thank you again!