Oil Change Formula

[TCRG]

Hello, I am working on a formula to indicate when an oil change is required. I have a cell that gives me starting mileage (d3), and a series of cells that give me mileage at fueling (b:b). What I am looking for is a way to calculate how close I am to my next oil change in cell j3. I know that the beginning of the formula would be =sum(b:b)-d3, but I do not know where to go from here. I would like the counter cell to reset each time 3000 miles is hit, and then start the countdown from there.

Thanks,
Brian

[JBeaucaire]

Maybe: =MOD(SUM(B:B)-D3, 3000)

[TCRG]

Thank you! This did work, although I did change the formula to =MOD(SUM(B:B)-D3, -3000) to give me a countdown as opposed to counting up. I was not familiar with the =MOD function.

This did also bring up two more questions if you would like to take a crack at them...

I am working on the Average MPG on this sheet, and I am not sure how to have the formula in cells that have yet to be calculated without it factoring into the grand equation. Also, is there any way to remove the "#DIV/0!" in an un-calculated cell so that it just appears blank? I tried an "=if(isblank" equation, but it did not do anything.

Thanks again!

[JBeaucaire]

THe DIV/0 error requires you check the denominator cell first, only apply the formula if something more than 0 is there.

=A1/B1

becomes

=IF(B1>0, A1/B1, "")


You can use the same thing to test the numerator and not run other formulas unless something is there:

=A1*24

becomes

=IF(A1=0, "", A1*24)