+ Reply to Thread
Results 1 to 9 of 9

Count a reoccurring number divisible by 70 over 24 months.

  1. #1
    Registered User
    Join Date
    06-06-2014
    Posts
    6

    Question Count a reoccurring number divisible by 70 over 24 months.

    I have made 24 sheets that track an increase in units developed from one month to the next and total on a summary sheet outlining the 24 months. At a point when any particular month reaches a total of 70 it needs to add 1 to another cell and that cell is then referenced to the summary sheet. This count continues to increase and over the 24 months can reach several multiples of 70. (140,210,280,350, etc)
    For example month one may have 5, month two has 48 and month 3 has 73, at this point a 1 (rounded down) would show as because 73/70 =(1)
    In month 4 it might be 210, I would want this to display (2) as 210/70 = 3 minus previous month 3 total of (1), and so on up to the 24 month point.

    I trust I have given enough info or explained it properly. As I am sure you have guessed I am not very good with excel but know that basically is possible with it!

    Thank you for your help!

    Regards,

    Warren

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,049

    Re: Count a reoccurring number divisible by 70 over 24 months.

    Hi and welcome to the forum

    It would probably help (for me at least) if you could upload a sample wof what you are working with and what you want?

    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    06-06-2014
    Posts
    6

    Re: Count a reoccurring number divisible by 70 over 24 months.

    I've posted my spreadsheet (condensed version) the cells in question are h37 on month one and h38 on month 2 referencing b67 & 68
    transferring data to the summary sheet line 17, each month needs to total the divisible number by 70 and then remove the difference from the number established the month before so no duplication of the findings is added to the total for any given month.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    06-06-2014
    Posts
    6

    Re: Count a reoccurring number divisible by 70 over 24 months.

    FYI sorry,i forgot to include that the final reference i C67, this then is picked up by the summary page for the entry on that page.

  5. #5
    Registered User
    Join Date
    06-06-2014
    Posts
    6

    Re: Count a reoccurring number divisible by 70 over 24 months.

    seems like i need 5 posts before I can ask anyone for help so here is number 4 - thank you!

  6. #6
    Registered User
    Join Date
    06-06-2014
    Posts
    6

    Re: Count a reoccurring number divisible by 70 over 24 months.

    seems like i need 5 posts before I can ask anyone for help so here is number 5 - thank you!

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,049

    Re: Count a reoccurring number divisible by 70 over 24 months.

    Actually, forum rules require you to wait at least a day before bumping your thread

    Having said that, you have had almost 70 views on this thread with no offers or suggestions This tells me that members dont really understand what you want, so perhaps try explaining again, and walk us through what you are doing

  8. #8
    Registered User
    Join Date
    06-06-2014
    Posts
    6

    Re: Count a reoccurring number divisible by 70 over 24 months.

    O.K. everyone, Sorry for my ignorance about excel but I will try to explain my needs more clearly. Also an offer goes out to anyone who would want to use join.me to walk through what I want.
    I have created a 24 month series of sheets each one pulling numbers from one month to the next to project profitability etc. The initial information is pulled from the bizinfo sheet. This is used by the user to input some basic numbers and then the calculations are handled automatically. I feel i have all of that down pat. Then all of this is referenced onto the summary sheet.

    On month one H37 and then on all subsequent months H38 is used to create a running total of the number of units that are generated over time.
    I created a formula in cell B67 to try to capture the number of units being generated over time but found that as soon as the inputs on the bizinfo sheet changes it changes the results in a manner that can't be predicted, therefore my formula in B67 would not always catch the unit cycling.
    Initially I need a formula in B67 that will look for key numbers beginning with 70 and when it does it creates a (1) in cell C67 If for example a number shows up in any given month that is equal to 73 then the remaining 3 would be carried over to accumulate until the number 210 shows up then (2), the next target is 490 would display (4), the next is 840 would display (8), the next 1680 would display (16), the next is 3360, would display (32), the next is 6720, would display (64), next key 13,540 would display (128) The key here is that any amount over the specified numbers would be carried over to the next month(s) to accumulate until the next key number is attained.

    Thank you again for your patience and understanding.

    Regards,

    Warren

  9. #9
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Count a reoccurring number divisible by 70 over 24 months.

    Advice => you used merged cells in you file,

    Don't use merged cells, you get in trouble with it sooner or later.



    Many sheets to look, long story to explain => but for me it is not clear what (and why) you want to achieve.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

+ 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. count number of months
    By Dariusd7 in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 01-28-2014, 03:57 PM
  2. [SOLVED] Using COUNT(IF(... to count number of months in years
    By Abid123 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-17-2014, 11:21 AM
  3. Reoccurring number deletion problem
    By JimiPockets in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-19-2012, 05:26 PM
  4. Sum of reoccurring names in a column ? Need to count each name only once..
    By syncguy in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-25-2012, 08:44 PM
  5. Showing if number is divisible...
    By njg2982 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-05-2007, 11:54 AM

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