Forum Discussion

Clifford2220's avatar
Clifford2220
Copper Contributor
Aug 18, 2023

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

 

Formulas and Functions
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Aug 18, 2023

    Clifford2220 

    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!

     

    Hope this will help you.

Resources