# Formulas

Copper Contributor

# Formulas

Looking for a formula that will tell a cell when a number needs to roll back over to zero.

Example: Column C cell - Column B cell = Column D cell, The column D answer is dropped back into the next cell down in  Column C.

Column C cell entry only can have 7 digits before it rolls back to zero. Looking for a formula to tell Column C how to roll over to zero and place this number in the next cell down in Column B

# Re: Formulas

To achieve this behavior where a number in Column C needs to roll over to zero and be placed in the next cell down in Column B when it reaches a limit of 7 digits, you can use a combination of formulas. Here is a step-by-step guide on how to set this up:

1. In cell C2, enter the initial number.
2. In cell D2, enter the following formula to calculate the difference between the numbers in Column C:

=C2-B2

3. In cell B3, enter the following formula to calculate the new number in Column B based on the rollover logic:

=IF(D2>=1000000, B2+1, B2)

4. In cell C3, enter the following formula to calculate the new number in Column C based on the rollover logic:

=IF(D2>=1000000, MOD(C2, 1000000), C2)

5. Copy cells B3:C3 down as needed.

Here is what each formula does:

• The formula in Column D calculates the difference between the numbers in Columns C and B. When the difference reaches or exceeds 1,000,000 (7 digits), it triggers the rollover logic.
• The formula in Column B checks if the difference in Column D is 1,000,000 or more. If true, it adds 1 to the value in the previous row of Column B. Otherwise, it keeps the same value.
• The formula in Column C checks if the difference in Column D is 1,000,000 or more. If true, it takes the remainder of the value in the previous row of Column C divided by 1,000,000. Otherwise, it keeps the same value.

By using these formulas, the numbers in Column C will roll over to zero and continue counting in Column B whenever the 7-digit limit is reached. The text, steps and functions were created with the help of AI.

My answers are voluntary and without guarantee!