Handling Data Rollovers

dwgnome

Active Member
Joined
Dec 18, 2005
Messages
441
We track fuel usage using a fuel meter (much like an odometer) and use a cell data validation equation to prevent erroneous numbers from being input – a form of data quality control. If the meter reading is not the same or higher than the previous day or days, then a Stop Error pops up. It has been working fine.

Now that the meter has rolled over (max is 999,999), the data validation won’t allow the user to enter the number. We do not want to simply add 1,000,000 to the numbers to keep it going, because differential math calculations are also being conducted on the data in a different area of the spreadsheet.

Is there a way to modify this equation to account for the occasional (every two to three years) roll-overs, yet have the equation generic enough to simply copy across all worksheets?

Data is arranged by month on separate worksheets. On each worksheet, the daily meter is input horizontally from left to right on the same row. Column C is Day 1, D is Day 2 etc.

DV:O18

Code:
[FONT=Calibri][COLOR=#000000]=IF(COUNTIF($B18:N$18,">0")<1,O18>=MAX(INDIRECT("'"&TEXT($B$3-1,"mmm")&TEXT($B$3-1,"yy")&"'!C18:AG18")),IF(OR(ISBLANK(O18),ISTEXT(O18)),TRUE,AND(ISNUMBER(O18),O18>=LARGE(N18:$C18,1))))[/COLOR][/FONT]

Use Excel 2010 on Win 7.


Example Table layout
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Col B
[/TD]
[TD]Col C
[/TD]
[TD]Col D
[/TD]
[TD]Col E
[/TD]
[TD]Col F
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Daily Fuel Meter Reading
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]4
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Storage Tank #1
[/TD]
[TD]997735
[/TD]
[TD]999700
[/TD]
[TD]2340
[/TD]
[TD]4788
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Forum statistics

Threads
1,223,230
Messages
6,170,883
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top