Tech Help for Averaging Quarterly Figures on Excel in an Ongoing Basis
Tech Help for Averaging Quarterly Figures on Excel in an Ongoing Basis
You were trying to customize Excel sheet (version 2007) so that it becomes easier to manage on a monthly basis.
You had business data for 1 year and corresponding fields for the quarterly averages. You were trying to to make it as user friendly as possible for anyone to update this sheet at the end of each month.
You were trying to make a feasibility study of how appropriate it would be to update just the new monthly figures and have the quarterly figure updated by itself automatically. The following data you already had for the accounting year 2009.
April 09 120 Q1-09 120
May 09 150 Q2-09 110
June 09 90 Q3-09 200
July 09 90 Q4-09 150
Aug 09 60
Sept 09 180
Oct 09 150
Nov 09 210
Dec 09 240
Jan 09 110
Feb 09 150
March 09 190
Now, the user would require average after each month for quarterly figures also. That is, until the first three months are completed of any quarter, first month will have the average equal to the monthly amount divided by 3. Next month will have sum of first two months of that quarter divided by 3 as quarterly average. Finally, third month which will complete that quarter will average final average for that quarter. Consequently, the user took help of his tech support provider who helped him formulate a running average on his excel sheet.
By making use of customized VBA coding, the following program that addressed the need of this Excel user was written upon the VBA editor (Alt+F11):
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column 2 Then Exit Sub
If Target.Offset(0, -1) = vbNullString Then
Target.ClearContents
Exit Sub
End If
If Not IsNumeric(Target.Value) Then Exit Sub
If Target.Value = 0 Then Target.ClearContents: Exit Sub
Dim Q As Integer, Qval As String, Qavg As Long, QC As Integer
Dim xRow As Long, yRow As Long, x As Long
Dim tYear As Long
Q = Quarter(Target.Offset(0, -1))
Qval = "Q" & Q & " " & Right(Year(Target.Offset(0, -1)), 2)
tYear = Year(Target.Offset(0, -1))
xRow = Target.Row
yRow = LastQ(Qval)
QC = 0
Qavg = 0
For x = 1 To Range("A" & Rows.Count).End(xlUp).Row
If Year(Range("A" & x).Value) = tYear And Quarter(Range("A" & x).Value) = Q And (Range("A" & x).Value vbNullString
And Range("A" & x).Value 0) Then
Qavg = Qavg + Range("B" & x).Value
QC = QC + 1
End If
Next x
If QC > 0 Then
Range("E" & yRow).Value = (Qavg / QC)
End If
End Sub
Private Function LastQ(Qval As String) As Long
Dim x As Long
For x = 1 To Range("D" & Rows.Count).End(xlUp).Row
If Cells(x, "D") = Qval Then
LastQ = x
Exit For
End If
Next x
If x > Range("D" & Rows.Count).End(xlUp).Row Then
Cells(x, "D") = Qval: LastQ = x
End If
End Function
Function Quarter(tDate As Date) As Integer
Quarter = Choose(Month(tDate), 1, 1, 1, 2, 2, 2, 3, 3, 3, 4, 4, 4)
End Function
Now, when the user entered 100 which was the data for April 2010, the quarterly average for Q1-2010 showed 33.33 automatically.
When the user entered 110 for May 2010, quarterly average showed 70. This is sum of April 2010 and May 2010 (100+110) divided by 3. When finally the user entered the data for the month of June 2010 which was 90, the quarterly average for the first quarter of accouting year 2010 showed 100. This is sum of April, May, and June 2010 figures divided by 3.
You too should try to customize your computations on Excel sheet by making use of in-house VBA codes. This will make your enterprise functions faster and give you competitive advantage in the process.
Steps to Managing Multiple Projects What HGH Can Do To Help With Aging? Skin Aging Questions & Answers Benefits Of Chemical Peel For Aging Skin Managing Your Time Starts with a Clean Desk Packaging Consultancy Managing Temporary Workers within the Construction Industry Cardboard Packaging The Importance Of Staging Why Use Document Imaging? Master The Aging Process And Live Like Your 20 Again Seven benefits of using protective packaging The Many Key Benefits of Using Cartons In Packaging