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

 

[Gerald Higgins]

Hi, welcome to the board.

To make it easier to understand, let's imagine you only had 5 countries, Brazil, Canada, Chile, Guam, Mexico.

What should the answer be, and why ?

 

[gregz1234]

Hi Gerald,

Thank you for the welcome and reply.

Well, I'm still essentially searching for what the result should be.

I'm trying to determine how to best conduct a weighted average of the days per shipment to destination (just one single number). I believe the 'weight' should probably be the shipment total for the country.

Destination Shipments Total Transit Days Days per shipment to Destination
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



thank you,
Greg

 

[Gerald Higgins]

OK, so if you're not sure what the right answer is, how will you evaluate whatever suggestion you get here ?

Let's go with your idea of using shipment total as the weight, what should the answer be for these 5 countries ?

 

[gregz1234]

This is true!

Well if I were to run a SUMPRODUCT formula, I would get 7.95.

This is fine, however if I were to add up the total transit days (5,630.29) and divide them by the total shipments (708) I get the same result of 7.95.

This leads me to believe I may have an issue on my weighted average?

 

[Dr. Demento]

Taking a step back from Gerald's suggestion, can you define your weighted average? A generic definition is "an average resulting from the multiplication of each component by a factor reflecting its importance." The factor can also be defined as a proportion - in this case, it could be that shipment total (by country) divided by the sum of all shipment totals or as you used SUMPRODUCT (# shipments * transit time??) (in which case, did you include the sum of all shipments??), MSDN has an example.

Best to start with your definition/formula and then work on how to accomplish it.

hth

 

[gregz1234]

Hi There and thank you for the reply,

In the exercise I'm running, I have several transit times to certain destinations.

I need to be able to provide an average time for all the times combined. I was told the average time should be 'weighted' in the sense that countries that receive more shipments, should have a higher "weight" in the total.

thank you,
Greg

 

[Dr. Demento]

You're looking for an average of rates or Harmonic Mean. Excel has a HARMEAN(range). I think Google just gave me a migraine Here's a pretty simplified explanation (halfway down the page)

BTW, I don't understand how you got your Days per shipment; when I do the calculation for Brazil, I get 15.4 days/shipment (unweighted).

In any case, I think some understanding of this will be in order, especially if you have to explain yourself!! Good luck!

 

[gregz1234]

My apologies, I accidently pasted the incorrect data into the final column here. Yes, 15.4 is correct:

Destination Shipments Total Transit Days Days per shipment to Destination
BRAZIL 171 2,636.14 15.42
CANADA 387 1,873.94 4.84
CHILE 24 159.85 6.66
GUAM 53 222.23 4.19
MEXICO 73 738.13 10.11


I will give the harmonic mean a try now, I will most likely be back with some questions (and potentially to share some migraine grief )

Thank you again!

 

[gregz1234]

quick question on HARMEAN, I don't believe this formula factors in any 'weight' when calculating. Any pointers, summaries on it as it relates to my data set?

Thank you again