Rolling Average in VBA with multiple columns

[johnokel]

I have this database that is updated on a daily basis. What I need is the VBA to have an in the two bottom cells of each column (rows 39 and 40) for it to give the daily Average for the month (Row 39) and for it to Average the last five business days taking count of the seven days prior totals (Row 40). (Some people work on off days) I also need to take into account company holidays. (Attached as well)
This will be recreated each month and the data for row totals can be transferred month for the needed crossover days at the beginning of each month. I have attached the actual database I am using.
I am completely clueless when it comes to VBA, my new boss gave me this as my first assignment and said it has to be done right now so any questions please ask and I will answer ASAP.
Thank you in advance.

[SamT]

First question: Why do you work for a boss like that? :D

That is not a Database, it is an ad hoc report, meaning that its structure changes periodically. You can work with that, but Heaven Forbid someone adds an extra empty Row between "Totals" and "Daily Average."

Just to be clear: You want the total of the 7 previous values, counting from today, divided by 5, unless there is an intervening holiday, then divide by 4, unless the holiday falls on a Saturday or Sunday, then divide by 5. Is that right?

Tell your boss to hire a programmer, because that is a complicated routine.

Nah, don't do that. Yet. Someone else here might find this an interesting challenge and take up your cause.

[johnokel]

I know he is difficult. I took the position with the intention of learning from him. I was surprised to find out his version of teaching was figure it out by this deadline.

Anyways I will control the report so I don't have worry about changes. So any idea how to do it so I don't get in trouble for not completing the task?:doh:



First question: Why do you work for a boss like that? :D

That is not a Database, it is an ad hoc report, meaning that its structure changes periodically. You can work with that, but Heaven Forbid someone adds an extra empty Row between "Totals" and "Daily Average."

Just to be clear: You want the total of the 7 previous values, counting from today, divided by 5, unless there is an intervening holiday, then divide by 4, unless the holiday falls on a Saturday or Sunday, then divide by 5. Is that right?

Tell your boss to hire a programmer, because that is a complicated routine.

Nah, don't do that. Yet. Someone else here might find this an interesting challenge and take up your cause.

[SamT]

Will your boss extend the deadline if you show him substantial Work In Progress?
Pick a mode: git'er'done 'n' tweak it later; Or do it right the first time.
How good of a VBA programmer do you want to become. See Q's 1 and 2.


If you choose to do it right the first time:

Start a Project Workbook, name the first sheet "Notes and Questions." name the third sheets "Daily Average Report Template."

First rule of VBA and Excel: Never use Merged Cells except of special pages to be printed that need a physical layout that cannot be accomplished without merging cells, create a separate print only page, if you have to. Do use Horizontal Alignment = "Center Across Selection."

Recreate that sample report sheet you uploaded without any values. Use 31 + 7 + 7 rows for the Dates column. Extras will be deleted later. Add the totals formulas. Add the two Average rows. Complete the rest of the sheet with all text, values, etc. Add a Cell in the top with "Report For:" as its text, Select the cell next to or under it and Insert >> Name >> Define. Name it "ReportMonth". This cell will be filled in later.
Note: while defining that Name, look at the "Refers To" box. Note that the Sheet Name is in there. Copy, with Ctrl+c, the entire string before the Range ID, including quotes and exclamation mark. Paste this, with Ctrl+v in front of the Range Name. click "add." This is a Sheet Specific Name.

Repeat this for the top Date Cell; Name = "TopDateCell" and the Bottom Date Cell; Name = "LastDateCell," and the entire totals Row; Name = "TotalsRow." If you feel that a Name really needs a Space, Use an UnderScore mark ("_") instead. Spaces aren't allowed.

Completely format the sheet as you wish. Do not Format the field of the Report as Accounting! Leave it Formatted as General. If you must, Format is as Currency. Use Menu >> Tools >> Options >> View Tab and uncheck "Zero Values."

Select the Cell in Column A next to the first Date Cell and use Menu >> Window >> Freeze Panes to keep the top of the sheet from scrolling out of view.

Tell you boss that you plan is to have a button that will:

Rename the CurrentMonth sheet according to its' relevant month
Make a copy of the Template Named "CurrentMonth"
Transfer the last weeks data from the old report to the copy.
Fill out the date column with the new month's dates, including the full last work week of the month.
Delete the unused Rows
Have all the Average formulas in place.
This will take 2 weeks to do it right the first time.


Do not tell your boss to check the start and end dates of Colour Entire Row Base on the Value of Two Other Cells (http://www.vbaexpress.com/forum/showthread.php?53037-Colour-Entire-Row-Base-on-the-Value-of-Two-Other-Cells)

[johnokel]

First thank you for your direction. As you can obviously tell I am pretty fresh at this.

1. No there is no moving the deadline line. It was originally stated as "Can you have it done by the 2nd changed let me rephrase that, you WILL have it done by then!"
2. So I think I will go with git'er'done 'n' tweak it later.

I know I am going to suffer the consequences for not doing this in VBA however, using the same "rules" is there a formula that I can just use in excel?

I was able to get it count correctly just by 5 days but, that doesn't use those additional 11 holiday days that are on the additional tab.

Here is the formula I used for that.


{=SUM(SUBTOTAL(9,OFFSET(C7:C36,LARGE(IF(C7:C36>0,ROW(C7:C36)-MIN(ROW(C7:C36))),ROW(INDIRECT("1:7"))),0,1)))/5}

This was entered on row 40 and dragged across columns C:AD.

[SamT]

Quick and dirty, Oh Boy! like a pigsty!. IT works, but I used a madeup date in june and don't know about the accuracy.
You have to manually color format the dates in column that are workdays( mon-fri) and fall on a holiday.



Const TotalsAddress As String = "B37" 'Adjust as needed for each report

Sub SamT()
'For help see: http://www.vbaexpress.com/forum/showthread.php?53065-Rolling-Average-in-VBA-with-multiple-columns

Dim StartRow As Long
Dim EndRow As Long
Dim Divisor As Long
Dim DailyAverageRow As Long
Dim LastCol As Long
Dim C As Long
Dim TotalCell As Range

Set TotalCell = Range(TotalsAddress)

EndRow = Range("B:B").Find(Format(Now - 1, "m/d/yyyy")).Row
StartRow = EndRow - 6
If StartRow < 7 Then StartRow = 7

Divisor = 4

If Range(Cells(StartRow, 2), Cells(EndRow, 2)).Interior.ColorIndex = xlColorIndexNone Then _
Divisor = 5

DailyAverageRow = TotalCell.Row + 3
LastCol = TotalCell.End(xlToRight).Column - 2

For C = 3 To LastCol
Cells(DailyAverageRow, C) = WorksheetFunction.Sum(Range(Cells(StartRow, C), _
Cells(EndRow, C))) / Divisor
Next C



End Sub

[johnokel]

You have no idea how much this is going to help, thank you!
I will try and play with this and just it for each report that I am doing since daily each line with be updated. This should buy me time though with my boss I'm hoping at least ease the trouble I get in. You have no idea. Again thank you.

[Aussiebear]

I know he is difficult. I took the position with the intention of learning from him. I was surprised to find out his version of teaching was figure it out by this deadline

I've seen dead crows with more leadership ability than your boss. Tell him to grab a brick in each hand and aim for each ear.