Weighted Average Question. Help would be greatly appreciated

[gregz1234]

Hi all,

I was looking for help on what seemed to be a relatively easy issue at first, however I just want to see if there are any other options available.


So, I'm currently need to come up with a weight average number for a set of shipment transit data, by country received.

Here is the data:

Destination Shipments Total Transit Days Days per shipment to Destination A/P
BRAZIL 171 2,636.14 5.86
CANADA 387 1,873.94 2.49
CHILE 24 159.85 6.13
GUAM 53 222.23 3.02
MEXICO 73 738.13 7.01
AUSTRALIA 278 1,966.42 3.80
CHINA 879 16,325.63 4.57
HONG KONG 277 1,642.65 3.33
JAPAN 260 1,563.31 3.25
KOREA 162 1,092.34 3.91
MACAU 182 1,232.85 3.40
MALAYSIA 69 456.71 3.32
SINGAPORE 116 651.95 3.34
TAIWAN, R.O.C. 123 688.81 3.74
THAILAND 34 560.55 2.62
AUSTRIA 23 102.10 3.61
BELGIUM 20 130.45 1.70
CZECH REPUBLIC 60 399.86 2.10
FRANCE 121 1,345.26 2.24
GERMANY 126 897.85 2.34
IRELAND 44 354.99 2.09
ITALY 97 512.31 3.76
NETHERLANDS 50 157.42 2.04
RUSSIAN FEDERATION 13 91.61 3.38
SPAIN 45 134.00 2.20
SWITZERLAND 57 268.55 3.51
UNITED ARAB EMIRATES 90 598.94 2.34
UNITED KINGDOM 105 884.88 2.26


I initially used a SUMPRODUCT formula, with the main array being days per shipment, and the 'weight' being the shipment totals themselves.

Unfortunately however, the result I'm getting is the exact same as a regular average (the sum of the transit days divided by the shipment total).


Any suggestions would be greatly appreciated, thank you all in advance.

Greg

 

[gregz1234]

Rather, would a weighted harmonic mean be preferred?

 

[Dr. Demento]

I believe harmonic mean would be the correct approach (making weights irrelevant). The basic idea you're attempting is an average of rates; harmonic mean seems to fit the bill.

That being said, the answer I came up with from your data was 90, which intuitively doesn't make any sense. However, that's also why I suggested learning more about this statistical measure; while it's likely the correct answer you'll need a solid grounding to help others understand.

 

[gregz1234]

Ah. Makes sense. Would it be worth it to check if a weighted harmonic mean is feasible?

The Harmonic mean I got was 6.59 (based on the transit times of 15.42, 4.84, 6.66, 4.19, and 10.11).

 

[Dr. Demento]

Yeah,sorry about that! End of the day brain fart. I was using the data from your original suggestion for SUMPRODUCT.

When I do it correctly, I get the same answer. Doh!!

 

[gregz1234]

Not a problem, it happens!

I'm a little concerned though when thinking about the harmonic mean. Wouldn't this formula be best used if the transit times were all to the same destination? I'm just a tad apprehensive after reading up on it. Would I be wise to at least find a way to add weights?

 

[Dr. Demento]

I'm not a mathematics authority (so take my advice for what it's worth, that is $0.02 ) However, it seems to me that you're calculating a rates (a change in some unit (shipments) divided by changes in time). From there, you're trying to get an average of the rates; from Wikipedia: Typically, it is appropriate for situations when the average of rates is desired.

From farther down the Wiki page: In transportation, to find the average speed of a trip over a route divided into constant speed segments (of distance) one must use the weighted harmonic mean (weighted by the distance of each segment). For example, if one travels half-way to a destination at 20 mi/hr , and then goes 60 mi/hr for the second half of the distance, the average speed is only 30 mi/hr (harmonic mean) and not the 40 mi/hr (arithmetic mean). This is because it took 3 times as long (in time) to go the first half of the trip distance as it did to go the second half and true average speed is a simple weighted arithmetic mean with the weights being time. Thus 20 mi/hr gets 3 times as much weight as 60 mi/hr: 3/4·20 + 1/4·60 = 30 mi/hr.
According the an explanation at BetterExplained: But don’t we need to know how far work is? Nope! No matter how long the route is, X and Y have the same output; that is, we move R shipments at speed X, and another R shipments at speed Y (italics are my substitutions for your example).

Bottom line is, however, you have to be comfortable with the quality of your work. If you feel weights is a more appropriate solution, the primary concern will be how to determine your weighting scheme. The usual reason to weight (at least in my field) is that your sample is not representative of the population at large and in order to compensate you weight. I'm not certain how you could determine if/how your sample is under/over-represented, or, if you have captured all the data points (and therefore have the entire population), weighting is unnecessary (because the sample looks exactly like the population) and adding weights will skew your results.

I do not mean to push you one way or the other. I hope you are comfortable with your end results (and do I know what it's like to have that nagging feeling "Did I do that right??"). If there's someone more knowledgeable and can weigh in (like what I did just there??), I'd be happy to be corrected. Then again, I can't make the Wiki example work with harmonic mean, so maybe I'm no help!

Good luck, brother.

 

[gregz1234]

Thank you for all the assistance with this matter!

 

[Dr. Demento]

Glad to help! For once, I'm giving back instead of asking