Large data set, need PROPER way to calculate averages

Question

I'm working on an excel sheet with 26k lines and need to calculate average costs per KG. I'm not sure which method of calculation to use as each is giving me different results.

For example:

(Col A)      (Col B)      (Col C)       (Col D)
Line         Weight       Cost       Cost per KG
====         =====        =====       =========
1            12           30           2.5
2            39           51           1.308
3            29           40           1.379

Do I calculate the average cost per KG by taking the average of Column D?

Method A: Avg(Column D) = 1.729

OR simply divide the total cost by the total weight?

Method B: Sum(Column C) / Sum(Column B) = 1.513

Without going into too much detail, my data set is giving me 7.451 (Method A), and 0.132 (Method B). HUGE difference.

Which method of calculation is correct?

Answer 1

It really depends on which "average" you mean when you say "average". If you specify unambiguously what quantity you need, it will be clear which calculation to do.

My expectation (ahem) is that you probably mean "total cost"/"total kg". That's generally what people need more often, and usually what people intend by the term 'average' in this situation, at least if not accompanied by further explanation.

It's also (sometimes) known as a 'volume-weighted average'.

Answer 2

When taking the average of $D$, you're actually taking the average of $ \frac{C}{B}$. That'd being said, Method B should be $ \frac{Sumproduct(1/B:B, C:C)}{Count(B:B)}$. Note I'm actually using $ Sumproduct(1/B:B, ... $ because there's no $sumdivision$ function in excel!