View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
John Michl
 
Posts: n/a
Default How to estimate a future service date?

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