0
$\begingroup$

Final edit: The answer is "yes, it's appropriate."

The specific problem I'm having seems to be a rounding error in Excel. See discussion on the accepted answer for the "proof" (but not a Proof, of course.)

Obviously, the unasked second question is "if yes, how do I do it correctly?"

Given my lack of luck in finding something similar online, I suspect I'm either making an error or using the wrong search terms. Or possibly both.

I have a number of hourly data points, in the form of (count, average time) which I've weighted into an hourly weighted average for four different sources.

These are then averaged into a daily average-per-source, and then those averages are averaged into a average-per-process (two different processes.)

I'm not worried about the validity of averaging out averages in this case - it gives me the sort of data I'm looking for - but I get different results depending on how I average them when it seems like they should be the same. This means to me that one of the methods is right, and the other is wrong.

Equations I'm currently using (note, they're being done in Excel, though I'm trying to make them at least readable):

Hourly weighted average:

  • R3 = ( (C3*D3) + (G3*H3) + (K3*L3) + (O3*P3) ) / Q3)
  • R4 = ( (C4*D4) + (G4*H4) + (K4*L4) + (O4*P4) ) / Q4)
  • ....
  • R26 = ( (C26*D26) + (G26*H26) + (K26*L26) + (O26*P26) ) / Q26)
  • C, G, K, and O are the counts. D, H, L, and P are the times. Column Q is the sum of C, G, K, and O.

Daily average:

  • S3 = (R3 + R4 + ... + R25 + R26) / 24
  • Average of the hourly weighted averages.

Average-per-process:

  • V7 = (S3 + S51) / 2
  • Average of two daily averages (S51 is a cell calculated similarly to S3 above.)

What I'm not sure of is whether it's valid to simply average out the hourly weighted averages as I did (similar question for averaging the daily into the "per-process" one,) or if I need to redo the weighted average process for them (i.e. (( count for hour 1 * average for hour 1) + ... ) / count per day)

$\endgroup$

1 Answer 1

Reset to default
1
$\begingroup$

Weighted average of timed statistics is appropriate but not to take the averages of the weighted averages. Such a statistic isn't really valid if it doesn't take into account the differences in the total daily times involved. Example, a cyclist travels at 8 mph for one hour and 20 mph for 2 hours on day 1 to average 16 mph. On day 2, the cyclist travels 5 hours at 24 mph. Well the average of the daily averages would be 20 mph. But the true average is 168/8 = 21 mph.

$\endgroup$
2
  • $\begingroup$ The total time involved in this case is 24 hours everywhere. So it's more like the cyclist travels 8mph for three hours and 20mph for two hours on day 1, then 5 hours at 24mph on day 2. ... which comes out the same when weighted or not, which makes me think I've got a problem in my logic somewhere. $\endgroup$
    – Epsilon
    Commented Jun 2, 2018 at 18:41
  • $\begingroup$ Yes, if the daily time periods are the same, then the average of the averages will be the same as the 2 day calculated average. If it isn't then something isn't correct elsewhere. $\endgroup$
    – Phil H
    Commented Jun 2, 2018 at 18:59

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .