Calculate Miles Driven Thus Far to Weekly Tracking Spreadsheet

[Hedy]

Hello, I am trying to create a spreadsheet to track weekly mileage for 22 people who use company vehicles. We are trying to achieve a goal for certain amount of miles driven without any accidents. The problem: I cannot figure out a formula to calculate the miles.

Not everyone provides mileage for every week because they might be on vacation, so in that case whatever the most recent miles they provide would be used.

I add a new column for each new week to this spreadsheet to the left of the column titled "Miles Driven Thus Far." I need to calculate the "Miles Driven Thus Far" column. Here is a mock up of the spreadsheet:


Name Vehicle No Odometer Reading 05/26/2014 Odometer Reading 06/02/2014 Odometer Reading 06/09/2014 Odometer Reading 06/16/2014 Miles Driven Thus Far
Joe 333 - 163,203 163,862 165,567
Mary 334 49,222 - 52,053 52,538
Bill - - 190,618 191,126

Thanks!

[6StringJazzer]

For row 2, miles driven thus far is

=MAX(C2:ZZ2)-MIN(C2:ZZ2)

This uses ZZ in place of knowing what the final column will be but it will work.

If there is no odometer reading for a date, do not put zero, leave it blank.

[Hedy]

This works - thank you so much!

[6StringJazzer]

You're welcome! If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."

If a member helped you solve your problem, consider adding to their reputation by clicking on the star icon below their name.