Checking Series for Anomalies

N

Not Excelling

I have a problem that I'm sure can be easily fixed, but I don't know
how to do it. I have a spreadsheet that we have to review each month,
with several columns of data. In one column are vehicle numbers, and
a few columns over is mileage for that vehicle. I need to check the
mileage for each vehicle (sometimes there is only one transaction,
other times, there may be 10-15 entries) and check for erroneous
mileage entries.

For example, if a vehicle had 45120 miles on 8/1, 48124 miles on 8/4,
then 47,128 miles on 8/6, I need to be able to quickly identify that
there is a problem. Currently, I'm having to manually check each row.
Is there a formula or macro I can use that will check, for each
vehicle number, (say, column D), the mileage entries in Column L, and
see if there is a negative growth from Row 1 to Row 2?

Currently, I have about 2,000 entries per month to check, so this is a
big time sucker.

Thanks!
 
S

smartin

Not said:
I have a problem that I'm sure can be easily fixed, but I don't know
how to do it. I have a spreadsheet that we have to review each month,
with several columns of data. In one column are vehicle numbers, and
a few columns over is mileage for that vehicle. I need to check the
mileage for each vehicle (sometimes there is only one transaction,
other times, there may be 10-15 entries) and check for erroneous
mileage entries.

For example, if a vehicle had 45120 miles on 8/1, 48124 miles on 8/4,
then 47,128 miles on 8/6, I need to be able to quickly identify that
there is a problem. Currently, I'm having to manually check each row.
Is there a formula or macro I can use that will check, for each
vehicle number, (say, column D), the mileage entries in Column L, and
see if there is a negative growth from Row 1 to Row 2?

Currently, I have about 2,000 entries per month to check, so this is a
big time sucker.

Thanks!

My Uncle Guido wants to know, "What's wrong with a little odometer
adjustment?" (^:

But seriously...

Without going into much detail, and assuming you have entries sorted by
date within vehicle, all you need is a formula like "=L2>=L1". This will
return TRUE for positive or zero growth, FALSE otherwise. Simple enough.

However, this will not work correctly at transitions between vehicles.
So this augmentation might suffice:
=IF(D1=D2,L2>=L1,TRUE)

Hope this gives you some ideas.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top