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.
