Weighted Average Help (Take 2)

gregz1234

New Member
Joined
Mar 16, 2016
Messages
28
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 ShipTransit DaysCountryShipment Total
20.531683.47China82
7.42452.35Macau61
11.97454.98Brazil38
4.27153.81Canada35
5.32186.09HK35
5.80191.25JP33
6.41173Korea27

<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~!! :biggrin:

-Greg
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,719
Office Version
  1. 365
Platform
  1. Windows
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
ABCDE
1Transit Days Per ShipTransit DaysCountryShipment Total
220.531683.47China82
37.42452.35Macau61
411.97454.98Brazil38
54.27153.81Canada35
65.32186.09HK35
75.8191.25JP33
86.41173Korea27
93294.95311
10
11Weighted Average10.595
12
13Example 2
14Transit Days Per ShipTransit DaysCountryShipment Total
1520.532053China100
167.42333.9Macau45
1711.97454.86Brazil38
184.27149.45Canada35
195.32170.24HK32
205.8191.4JP33
216.41179.48Korea28
223532.33311
23
24Weighted Average11.358
25
Sheet
 

gregz1234

New Member
Joined
Mar 16, 2016
Messages
28
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,909
Messages
5,598,796
Members
414,259
Latest member
beetle12

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top