Tallying a running total of usage

[cadamhill]

Does anyone know what I can do if when using a set of continuous numbers that autoreset at 99,999,999, i need to calculate totals between start and stop dates.

Example. Instead of using 99,999,999 we will auto restart our numbering after 4. It will start back at 1.

Scenario 1 -----Suppose my Start date is Jan 1, 2014 and my Stop date is Jan 3, 2014. Then my Stop date-Start date = 2

Scenario 2------However, if my Start date is Jan 4, 2014 and my Stop date is January 6, 2014 then my Stop date-Start date = -1.

With scenario 2, the correct answer should be 2 as well. As, the ticker only moved 2 spaces, its just that the numbering started over. If the numbers were continuous to infinity, it would have moved from 4 to 6. Somehow I need to represent this using the attached function as a starter and adding to it. The sample attached text resets at 99. Thanks for any help.

See Example attached.

Text text.xlsx


Jan 1, 2014 .....1
Jan 2, 2014 .....2
Jan 3, 2014 .....3
Jan 4, 2014 .....4
Jan 5, 2014 .....1
Jan 6, 2014 .....2
Jan 7, 2014 .....3

[Leith Ross]

Hello cadamhill,

Add this formula to "D7" and drag it down.

=MOD(D6, 99) + 1

[cadamhill]

Can this be accomplished as a single function attached to the vlookup function? Ultimately I want to change this over to PHP, so I will need to have it more in the coding. Any suggestions? Thanks for your help at any rate.

[cadamhill]

I see, so there must be some modify type statement in PHP as well (not that excel and PHP are anything alike). It should be easy to figure out though. thanks for your help.

[cadamhill]

I looked over what you suggested and I don't think that will work. What I need to do, from a calculation standpoint, is have the calculation look at any 1 followed by a number ending in 99 and it should read the number ending in 99 +1 ( in the calcluation only). If that makes sense. The only thing I can't do is re-label cell values of the number after 99,999,999, equal to 1 M. It has to remain 1, but to the math formula has to resemble 1 M. So, in short, I am looking for a math function that accomplish's this rather than a complete manipulation of the value in the cell. Thanks for any help.

[Leith Ross]

Hello cadamhill,

Code this in PHP? You know this Excel forum is for VBA and Excel formula questions, don't you?