1

I have a sheet that contains the distance driven and the time. These are in columns B (distance) and C (time). Calculating the average speed is simple. Sum the distance and the time and divide the total distance driven by the total time used.

But how do I calculate the minimum speed and the maximum speeds?

To give an example: My last three rides have been 45,2 km/2:10 h, 13,6 km/0:45 h and 25,5 km/1:15 h. Out of these three, the first one has the greatest average speed of 20,9 kmh and the second the lowest at 18,1 kmh. How do I show this data in a cell? Do I need a script for that? If so, what should that look like?

2

1 Answer 1

2

You can do this with an array formula. Where B2:B10 are your distances and C2:C10 are your times, paste the following into the formula bar and press Ctrl+Shift+Enter.

=MAX(B2:B10/C2:C10)

The special array entry will make Excel interpret B2:B10/C2:C10 as the array

{B2/C2, B3/C3, ..., B10/C10}

The MAX (or MIN) function then finds the maximum (or minimum) value in this array.

3
  • Thanks! This works quite good, but when I add new values, it doesn't update automatically but instead a small green mark appears on the top left corner to ask if I want to update the formula to contain the added rows. How do I automate this? Also, I had to format the formula to this: {=MAX(B2:B19/((C2:C19)*24))} Thats because there's something strange going on with the fields formatted as time.
    – MikkoP
    May 21, 2014 at 15:54
  • 1
    After a little bit of research, I came up with this for the maximum speed: {=MAX(INDIRECT("B2:B" & COUNTA(B2:B1000) + 1) / (INDIRECT("C2:C" & COUNTA(C2:C1000) + 1) * 24))} Though there are still some hard coded values, but it's good for years. This seems to work for now, but do you see if there's anything that could cause problems?
    – MikkoP
    May 21, 2014 at 16:07
  • @MikkoP That's probably the best way to go. You could also format your data as a table. Excel will then extend the table as you add new rows. Your formula would then have to refer to the fields of the table instead of hard-coded column addresses. That should make sure your formulas include any added data.
    – Excellll
    May 21, 2014 at 18:55

You must log in to answer this question.

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