Results 1 to 7 of 7
Thread: Moving sums (Excel XP)

20080618, 15:09 #1
 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 tabsone for each month of the year. On each monthly tab, in column A, is the numbers 1 through 31 starting in A5 and downwardthese are the day numbers for each month. I have 22 other columns that capture daily production activity. The plant operates 7 days a weekexcept for an August two week shutdown 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 activityMarch 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 3day 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 monthso for the first two days of the monththe 3day 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.

20080618, 15:27 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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?

20080618, 16:37 #3
 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.

20080618, 16:54 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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>

The Following User Says Thank You to HansV For This Useful Post:
chinmay (20140313)

20080618, 17:10 #5
 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

20080618, 17:27 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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 (n1) 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 (n1) 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).

20080618, 17:33 #7
 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 solutionsso those that want to learnlike me have an opportunity to do so....again, I am truly GRATEFUL your help. Many thanks!!! JimC