Rollover Calculation Integration into formula (Help Please & Thanks)

JGCAD

New Member
Joined
Aug 8, 2019
Messages
5
Morning All,
I am wondering if there is a way to account for roll over numbers.
I use monthly meter readings: I subtract the previous reading from the current reading to get the total usage during the billing period. But the numbers roll over once it reaches 100,000 or 1,000,000.

Here is my example (I also attached an image of the workbook):
C4 is the current meter usage
D4 is the most recent meter reading
E4 is the meter reading prior to the most recent meter reading

C4=D4-E4
D4=INDEX(G4:OB4,1,COUNTA(G4:OB4))
E4=INDEX(G4:OB4,1,COUNTA(G4:OB4)-1)

How do I account for when the meter rolls over in my calculations? I'm basically trying to have everything auto-populate other than entering the new meter reading data each new month. This data will in turn populate the individual client invoices.

Any ideas would be super as I feel like I've gone down a bit of a rabbit hole on this one, lol!
Thanks,
Jenn
 

Attachments

  • Example.png
    Example.png
    94.8 KB · Views: 119

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
To get the rollover amount, try =MOD(D4-E4,F4) where F4 is the rollover point (100,000 1,000,000 etc).

For D4 and E4 I would suggest using =INDEX(G4:OB4,MATCH(1e+100,G4:OB4)) and =INDEX(G4:OB4,MATCH(1e+100,G4:OB4)-1) respectively. This will ensure accurate results in the event that there are any empty cells (missing readings).
 
Upvote 0
To get the rollover amount, try =MOD(D4-E4,F4) where F4 is the rollover point (100,000 1,000,000 etc).

For D4 and E4 I would suggest using =INDEX(G4:OB4,MATCH(1e+100,G4:OB4)) and =INDEX(G4:OB4,MATCH(1e+100,G4:OB4)-1) respectively. This will ensure accurate results in the event that there are any empty cells (missing readings).

Brilliant!!! My most sincere thanks, your help is truly appreciative and it appears to function perfectly:)
 
Upvote 0
Brilliant!!! My most sincere thanks, your help is truly appreciative and it appears to function perfectly:)

It seems through my testing the roll over limit something's not quite right and I'm not sure why. I applied your recommendation and if you'd like to take a peek at the image, it's not calculating C5, C6, C7, C8.
Any ideas?
 

Attachments

  • Example.JPG
    Example.JPG
    125.5 KB · Views: 43
Upvote 0
To get the rollover amount, try =MOD(D4-E4,F4) where F4 is the rollover point (100,000 1,000,000 etc).

For D4 and E4 I would suggest using =INDEX(G4:OB4,MATCH(1e+100,G4:OB4)) and =INDEX(G4:OB4,MATCH(1e+100,G4:OB4)-1) respectively. This will ensure accurate results in the event that there are any empty cells (missing readings).

It seems through my testing the roll over limit that there something's not quite right and I'm not sure why, I'm must be doing something wrong. I applied your recommendation and if you'd like to take a peek at the image, it's not calculating C5, C6, C7, C8.
Any ideas?
 

Attachments

  • Example.JPG
    Example.JPG
    125.5 KB · Views: 30
Upvote 0
Did you make any changes to the formula?
From the screen cap it looks like those rows are working on a rollover of 100,000 instead of the correct 1,000,000
 
Upvote 0
Did you make any changes to the formula?
From the screen cap it looks like those rows are working on a rollover of 100,000 instead of the correct 1,000,000
Apologies Jason, I see my mistake now; where F4 is the rollover point, it kept F4 as the value when I pulled the formula down versus associating it with the correct row. I should have caught that prior to replying. Goodness, sometimes it's best to step away and come back to it for a fresh look.

Cheers and thanks for all your help:)
Jenn
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,240
Members
448,555
Latest member
RobertJones1986

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