loan

subject: Tech Help for Averaging Quarterly Figures on Excel in an Ongoing Basis [print this page]

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.

welcome to loan (http://www.yloan.com/)