I’m having trouble with my Google Sheets calculations and can’t figure out what’s going wrong.
I’m tracking cycling data for a 90km ride that I broke down into 5km segments. For each segment, I enter the time it took to complete that 5km portion. Then I have formulas that calculate the speed in km/h for each segment.
The problem comes when I try to get the overall average speed. I’m using the AVERAGE function on all the individual segment speeds, but the result doesn’t match what it should be. For example, one of my rides shows an average speed of 31 km/h when it should actually be around 28 km/h.
I also calculate the total time using SUM and that seems correct. I thought maybe it was a rounding issue so I tried changing the number format to scientific notation, but that didn’t help.
What could be causing this discrepancy in my average speed calculation? Is there something wrong with how I’m averaging the individual segment speeds?
You’re using arithmetic mean instead of harmonic mean - that’s why your numbers are off. When you rode fast on one segment and slow on another, simple averaging treats them equally. But you actually spent way more time at the slower speed, which drags down your real average. Your method works great for test scores where everything’s weighted the same, but not for speed calculations. Just take your 90km and divide by total time in hours - you’ll get around 28 km/h, which is what actually happened.
You’re making a basic math mistake with how averages work for speed calculations. When you average individual segment speeds, you’re weighing each segment equally - doesn’t matter if one took way longer than another. This skews things toward segments where you were going slower or faster. For real average speed, just use the basic formula: total distance ÷ total time. You’ve already got the total time with SUM, so divide your 90km by that number. That’s your actual average speed. That’s why you’re seeing 31 km/h instead of 28 km/h - averaging the segments inflates the result when your times vary a lot. Speed is just total distance over total time, nothing fancy.
classic misteak! u can’t just avg the speeds - each segment takes diff time. u need total dist divided by total time, not avg of indiv speeds. that’s y ur getting 31 instead of 28.