+ Reply to Thread
Results 1 to 15 of 15

Formula to add +2 to the cell, but to reset once the counter would exceed 9

  1. #1
    Registered User
    Join Date
    06-29-2021
    Location
    Arizona
    MS-Off Ver
    2008
    Posts
    7

    Formula to add +2 to the cell, but to reset once the counter would exceed 9

    I am in the process of trying to create a pinning calculator for locksmithing, and I'm running into an issue where I can't have the number exceed single digits, so I'm wanting to have it reset to 0 or 1 based on if the next number would be.


    Here's an example of what I'm trying to do.


    2 3 4 5 6 7 8
    2 3 4 5 6 7 0
    2 3 4 5 6 7 2
    2 3 4 5 6 7 4
    2 3 4 5 6 7 6

    What it's currently trying to do:

    2 3 4 5 6 7 8
    2 3 4 5 6 7 10
    2 3 4 5 6 7 12
    2 3 4 5 6 7 14

    Is there a formula to keep that from happening, or will I just simply have to fix any numbers that exceed single digits?

    I'm fine if there's a setting that will apply this rule to an entire sheet, as long as it doesn't affect separate sheets.


    Right now, I'm just using "Conditional formatting" to flag any number greater than 9, so it's made obvious what cells are exceeding that limit.
    Attached Files Attached Files
    Last edited by SinfulPain; 06-29-2021 at 12:30 PM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Formula to add +2 to the cell, but to reset once the counter would exceed 9

    Welcome to the forum.

    There are instructions at the top of the page explaining how to attach your sample workbook.

    Please take a moment to check your Excel version (File | Account ...) and update your forum profile.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Formula to add +2 to the cell, but to reset once the counter would exceed 9

    Where are the numbers coming from? how are they generated?? Are the cells in a row in ONE cell or several???
    Last edited by Glenn Kennedy; 06-29-2021 at 12:02 PM.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Formula to add +2 to the cell, but to reset once the counter would exceed 9


  5. #5
    Registered User
    Join Date
    06-29-2021
    Location
    Arizona
    MS-Off Ver
    2008
    Posts
    7

    Re: Formula to add +2 to the cell, but to reset once the counter would exceed 9

    Quote Originally Posted by Glenn Kennedy View Post
    Where are the numbers coming from?
    The bitting for keys are always 0-X, or 1-X, never exceeding 9.


    Quote Originally Posted by Glenn Kennedy View Post
    how are they generated??
    By the user (I choose what initial bitting I want to use, and then have Excel extrapolate it, by increasing the pin depth by 2, until it rolls over)

    Quote Originally Posted by Glenn Kennedy View Post
    Are the cells in a row in ONE cell or several???
    Several rows. 1 row per key. (with a master keyed system you can have in theory, hundreds, or thousands of "change keys").

    I'll figure out how to upload the sample workbook in a minute. I was doing some housekeeping on the spreadsheet.

  6. #6
    Registered User
    Join Date
    06-29-2021
    Location
    Arizona
    MS-Off Ver
    2008
    Posts
    7

    Re: Formula to add +2 to the cell, but to reset once the counter would exceed 9

    Quote Originally Posted by JohnTopley View Post
    See ...assalock....
    Unfortunately, that is a 100% manual sheet. I'm working on building a mostly automated sheet, where I only have to set the Master key, initial Change key, and the Control key, then it automates the rest.

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Formula to add +2 to the cell, but to reset once the counter would exceed 9

    Understand. Interested in seeing the automation.

  8. #8
    Registered User
    Join Date
    06-29-2021
    Location
    Arizona
    MS-Off Ver
    2008
    Posts
    7

    Re: Formula to add +2 to the cell, but to reset once the counter would exceed 9

    Quote Originally Posted by JohnTopley View Post
    Understand. Interested in seeing the automation.
    File is posted for you to look at.

  9. #9
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Formula to add +2 to the cell, but to reset once the counter would exceed 9

    So what is (are) the rule(s) if a value exceeds 9?

  10. #10
    Registered User
    Join Date
    06-29-2021
    Location
    Arizona
    MS-Off Ver
    2008
    Posts
    7

    Re: Formula to add +2 to the cell, but to reset once the counter would exceed 9

    Quote Originally Posted by JohnTopley View Post
    So what is (are) the rule(s) if a value exceeds 9?
    Right now, it just puts a red dot in that cell.

    I'm wanting it to roll over to 0, so if the cell was 9, then the next cells would be 1, 3, 5, etc.

    If 8, then it'd be 0,2,4,6. Does that make sense?

  11. #11
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Formula to add +2 to the cell, but to reset once the counter would exceed 9

    Referring to your file: C14=10, but as C13=8, C14=0?

    D22=11, D21=9 therefore D22=1 ?

    So it is not simply if > 9 ... my understanding of post #10.

    Can you please add (a sample of) the corrected values to your posted file.

  12. #12
    Registered User
    Join Date
    06-29-2021
    Location
    Arizona
    MS-Off Ver
    2008
    Posts
    7

    Re: Formula to add +2 to the cell, but to reset once the counter would exceed 9

    Quote Originally Posted by JohnTopley View Post
    Referring to your file: C14=10, but as C13=8, C14=0?

    D22=11, D21=9 therefore D22=1 ?

    So it is not simply if > 9 ... my understanding of post #10.

    Can you please add (a sample of) the corrected values to your posted file.
    Correct, that's the way I'm wanting to have it work (if possible. I'm fairly new to excel, and I know this is probably a more unusual use for its formula capabilities, as I've never seen one like that so far)

  13. #13
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Formula to add +2 to the cell, but to reset once the counter would exceed 9

    This ??

    example in D20

    =IF(D19+2>9,D19-8,D19+2)

    other error changed (but not all cells have formula changed)
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    06-29-2021
    Location
    Arizona
    MS-Off Ver
    2008
    Posts
    7

    Re: Formula to add +2 to the cell, but to reset once the counter would exceed 9

    Quote Originally Posted by JohnTopley View Post
    This ??

    example in D20

    =IF(D19+2>9,D19-8,D19+2)

    other error changed (but not all cells have formula changed)
    That's exactly the fix!

  15. #15
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Formula to add +2 to the cell, but to reset once the counter would exceed 9

    you're welcome.

    If your problem is solved can you please mark as SOLVED (see "Thread Tools" at top of page)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Reset Counter
    By Tuca in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-28-2019, 07:39 AM
  2. Reset counter for equal to and more than 0
    By Darkestvalkryie in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-13-2018, 12:39 AM
  3. Replies: 1
    Last Post: 08-20-2014, 05:45 PM
  4. For loop counter reset
    By amphinomos in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-03-2014, 10:59 AM
  5. Counter reset
    By BN-CD in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-13-2007, 04:56 PM
  6. Counter Reset??
    By loh69 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-28-2006, 07:39 AM
  7. [SOLVED] Reset Counter
    By Ed in forum Excel General
    Replies: 3
    Last Post: 04-14-2006, 12:40 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1