Is there a way...

[salgud]

to do this without going to VBA?

I track the Division vehicle for our group. Every 5,000 miles it needs an
oil change. I have a spreadsheet to track the mileage which I enter every
month near the end of the month.

Date Mileage Status
7/30/09 10,000 oil change
8/29/09 11,200
9/28/09 13,300
10/30/09 15,001

I'm trying to figure out if there is a way using a formula to tell me when
the difference between the latest mileage entry and the last oil change
mileage is greater than 5,000 miles, as it is in my example on 10/30.
(10/30 mileage is 15,001, last oil change was at 10,000). Or is this a job
for some VBA code?

I can write the code, just have it activated whenever a new mileage entry
is made and have it calculate the difference between the new mileage and
the previous oil change mileage and give me a Msgbox telling me when an oil
change is due. But I am wondering is it can be done with a formula. Not
sure how to tell it to find "oil change", or even the previous text entry
in that column, then do the calculation from that inside an if statement
having a message if the difference exceeds the 5,000. Anyone know how to do
this, or even if it's doable?

Thanks in advance.

 

[Teethless mama]

Assume Date in column A, Mileage in column B, and Status in column C

In E2: =IF(LOOKUP(10^10,B:B)-LOOKUP(2,1/(C:C="oil change"),B:B)>5000,"need
oil change","")

 

[Bernard Liengme]

Here is one way
date miles since change change warning
07/30/2009 10,000 0 X07/31/2009 11,200
120008/01/2009 13,300
330008/02/2009 15,001
0 X08/03/2009 16,702
170108/04/2009 18,403
340208/05/2009 20,104
5103 Oil change needed

In C2 (cell under "since change") I have this formula
=B2-MAX(IF($D$22="X",$B$2:B2))
This is copied down the column
In E2 (under "warning" I have
=IF(C2>=5000,"Oil change needed","")
this is copied down the column
You could hide column C to make worksheet look better
Email me (get my address from my website) and I will send you sample file
best wishes

 

[salgud]

Assume Date in column A, Mileage in column B, and Status in column C

In E2: =IF(LOOKUP(10^10,B:B)-LOOKUP(2,1/(C:C="oil change"),B:B)>5000,"need
oil change","")

Thanks for your reply. I'm getting a #NUM error on the term (C:C="oil
change"). Any ideas?

If you have the time, can you explain the LOOKUP(2,1/(C:C="oil change")
part of the equation? What is the reciprocal of the (C:C="oil change")?

 

[Dave Peterson]

You can only use the entire column in xl2007.

So use a range that's big enough for your data:

=IF(LOOKUP(10^10,B1:B111)-LOOKUP(2,1/(C1:C111="oil change"),B1:B111)>5000,
"need oil change","")

 

[salgud]

You can only use the entire column in xl2007.

So use a range that's big enough for your data:

=IF(LOOKUP(10^10,B1:B111)-LOOKUP(2,1/(C1:C111="oil change"),B1:B111)>5000,
"need oil change","")

Thanks, Dave. Works great!