Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Moving sums (Excel XP)

    I have a production spreadsheet with 12 tabs--one for each month of the year. On each monthly tab, in column A, is the numbers 1 through 31 starting in A5 and downward--these are the day numbers for each month. I have 22 other columns that capture daily production activity. The plant operates 7 days a week---except for an August two week shut-down for maintenance. In row 50 is the total formula for each column. Everything is straightforward and the totals are accurate.

    I have been asked to calculate in row 52, column totals for the last 3 days of activity only. So for example, if I am on the March tab and it is March 4th, I need to add the amounts in each column for March 2 - 4 activity. The formula also has to be "smart" enough to know if there are less than 3 days of activity in the current month [i.e. if the date is March 2nd, then do not calculate as there are only 2 days of production activity---March 3 would the first time the formula would be used].

    I have looked at several other posts via search, but to be honest I am lost as to whether this can be done via formulas or I need to enter the merky waters of VBA. I am also struggling [i.e., lost ] with "how" to ensure the 3-day calculation only produces a number when there are at least 3 days of activity in the month [i.e., management looks at this report and I have been told NOT to have anything display in the 3 day calculation row until there are 3 days of activity in the current month--so for the first two days of the month--the 3-day calculation row should be blank].

    I would also like to the ability to add flexibility to the formula or VBA. In talking with my boss, the 3 day totals are only a starting point with management. After a month of analysis, management may decide to use 4 day totals or even 5 day totals in place of the 3 day approach...Thanks for any advice....If this needs done via VBA, please try to explain the code so I can learn how it works and can adjust it should my assumptions change [i.e. 3 day becomes 4 or 5 days]. Thank you again.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Moving sums (Excel XP)

    Do you want to determine the "last 3 days of activity" based on the current date, or on the data that have been entered?

  3. #3
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Moving sums (Excel XP)

    Hans,
    Good question..sorry I didn't address this my initial post.

    The column data is blank until it is manually typed in by the shift operator, so could we use the last 3 days entered versus entry of a date? That way, there is no change to the shift operator's Excel instructions nor would I have to worry about an incorrect date entry by the shift operator that would produce an erroneous calculation.

    I am hoping that I have given you the "right" answer so that it can be done in EXCEL..I have really struggled over the last couple of days to make this work...a very humbling experience for what started out to be something I thought that I could do. THANKS.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Moving sums (Excel XP)

    This can be done with formulas. See the attached example. The number of days to sum can be entered in cell B54. The formula in B52 for the sum of the last n days is
    <code>
    =IF(COUNT(B$5:B$35)<$B$54,"",SUM(OFFSET(B$4,COUNT( B$5:B$35)-$B$54+1,0,$B$54,1)))</code>
    Attached Files Attached Files

  5. The Following User Says Thank You to HansV For This Useful Post:

    chinmay (2014-03-13)

  6. #5
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Moving sums (Excel XP)

    Hans
    Thanks!! I obviously need to read up on the offset command...I will spend some more time in the forum [there are many, many matches when I search for "offset" and Excel's help file...to understand this function...I noticed that you add 1 to cell $b$54 prior to the substraction in the sum formula using offset....can you shed some light on why? Again, thank you!!! JimC

  7. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Moving sums (Excel XP)

    B4 is the cell above the data.
    COUNT(B$5:B$35) is the number of cells containing numbers. Let's say this is 6, because B5, B6, B7, B8, B9 and B10 contain numbers.
    The last filled cell is B10 - an offset of 6 from B4.
    If we want to sum the last 3 cells, we start at B8 - this is 2 cells above the last filled cell.
    If we want to sum the last 5 cells, we start at B6 - this is 4 cells above the last filled cell.
    As you see, if we want to sum the last n cells, we start (n-1) cells above the last filled cell.
    The OFFSET formula moves COUNT cells down from B4 to end on the last filled cell, then it moves up (n-1) cells where n is the value of B54.
    The OFFSET formula also uses the Height and Width arguments - Height is equal to n (i.e. B54) and Width is 1 (we want to sum cells in one column).

  8. #7
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Canton, Ohio, USA
    Posts
    270
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Moving sums (Excel XP)

    Hans,
    Thanks for all of your efforts in the lounge....and thanks again for helping me solve a problem and learn something new at the same time....

    As I have shared in other posts, I am truly amazed by your talent, your willingness to share it, and the patience you have to explain solutions--so those that want to learn--like me--- have an opportunity to do so....again, I am truly GRATEFUL your help. Many thanks!!! JimC

Posting Permissions

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