How to estimate a future service date?

[Monkey.wrench]

I have a gas mileage worksheet for my truck and I want to figure out the
estimated future oil change date. Column A is the date of fuel fillup,
Column B is the total mileage. I change my oil every 3,000 miles. I
would greatly appreciate some help, if you need to see the spreadsheet,
just ask and I can send it...wouldn't figure it would be difficult to
figure, but I've been working on a solution for about a month now....

 

[John Michl]

There are a number of ways to approach this, some more elegant than
others. Here's one simple approach that may or may not fit into your
current spreadsheet. Assume the following:
A1 = Mileage Last Change
A2 = A1 + 3000 (or Mileage Next Change)

A5 ~~ A100 Date of Fill
B5 ~~ B100 Odometer Reading
C5 ~~ C100 Moving average of miles per day
D5~~D100 Prediction of next oil change date

Decide how many fills you want to include in the moving average of
miles per day. I'll say three which means that...
C7 = (B7-B5) / (A7-A5) or Miles over last three fills / days
transpired
D7 = A7 + ($A$2 - B7 ) / C7 or Last Fill Date + Number of days to drive
remaining miles

This should get you started. If you don't care about the moving
average (which would be more accurate) you could eliminate columns C
and D and do those calcs for all data in the sheet. You probably could
use array formulas and dynamic named ranges to calculate the moving
averages, too.

Hope that helps.

- John Michl