A delivery van goes 60 km out at 30 km/h and travels the same distance back at 60 km/h. The two speed readings average to 45, but the van spends more time at the slower speed. Its average across the trip is 40 km/h.

Calculate the equal-distance case

Put 30 and 60 in B2:B3 and use this formula:

=HARMEAN(B2:B3)

The harmonic mean is appropriate here because the two distances are equal and both speeds are positive.

Two equal 60 km legs take two hours at 30 km/h and one hour at 60 km/h.

Illustrated route. The slow leg takes twice as long.

Rebuild the answer from travel time

Divide 60 by 30 for the outbound time of two hours. Divide 60 by 60 for one hour back. The check is 120 km ÷ 3 h = 40 km/h. For unequal distances, calculate each leg’s time from its own distance and speed, then divide the sum of distances by the sum of times. Include stop time in the denominator if your question is about the whole elapsed trip.

The entire 120 km trip takes three hours, giving 40 km/h.

Illustrated reconciliation. Use the total distance and total elapsed travel time.

Microsoft’s Excel function reference