PDA

View Full Version : Average Function-Cannot do Copy and Drag with Quarterly Avg. Calc - Periodicity Issue



kewiopex
11-18-2016, 05:56 AM
Hello Specialists
I have been trying to devise a method with the Average function (see attached workbook with tab named Quarterly to do a copy and paste of the cells when I need to expand each of the rows for the various categories. However, because of the periodicity of the monthly values, I cannot do the copy and drag. I have tried to use an Index function within the Avg function but again this only worked within one quarter when doing the copy and drag pasting.
For a given category, for a given quarter, the avg takes a value for column CN over 3months so that as an example, from cell 1, cell 7, and cell 13 as there is a 6 cell gap. The issue is that due to the periodic nature of the information it does not work . The next quarter for CN would be cell posion19, 25, 31.
Any help would be greatly and enthusiastically received

kewiopex
11-19-2016, 08:43 AM
All
There has been some work on sites using an index formula but in trying to adapt it, I could not do it. The function
=AVERAGE(INDEX(Monthly!D$5:$ZZ$5,1,(1+(18*(COLUMN()-3)))),INDEX(Monthly!D$5:$ZZ$5,1,(7+(18*(COLUMN()-3)))),INDEX(Monthly!D$5:$ZZ$5,1,(13+(18*(COLUMN()-3))))) will only work for copying and dragging in the first 6 columns of the quarterly, but not further unless it is modified again to allow for copying the function.
I am at wits end and wonder if it can be done by function or better with vba. If need be I will also eliminate the Row Avg column to simplify further.
Please guide me.

Paul_Hossler
11-19-2016, 04:20 PM
I think a sub would be the easiest

This is very dependent on the assumed fixed layout of the monthly data




Option Explicit

Dim wsQ As Worksheet, wsM As Worksheet
Dim iLastRow As Long

Sub SpecialAverage()
Dim iMonCol As Long

'delete old quarterly
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Quarterly").Delete
Application.DisplayAlerts = True
On Error GoTo 0

'make quarterly based on monthly
Set wsM = Worksheets("Monthly")
Sheets("Monthly").Copy After:=wsM
Set wsQ = ActiveSheet

With wsQ
.Name = "Quarterly"
.Cells.UnMerge
.Cells.WrapText = False

'end of data
iLastRow = .Cells(5, 1).End(xlDown).Row

iMonCol = 3
Do While Len(.Cells(3, iMonCol).Value) > 0
Call MakeQtr(iMonCol)
iMonCol = iMonCol + 6
Loop
End With


End Sub


Private Sub MakeQtr(c As Long)
Dim iRow As Long, iCol As Long
Dim vMonYr As Variant


'qtr title
vMonYr = Split(wsQ.Cells(3, c).Value, " ")
Select Case LCase(vMonYr(1))
Case "january"
wsQ.Cells(3, c).Value = "1Q " & vMonYr(0)
Case "april"
wsQ.Cells(3, c).Value = "2Q " & vMonYr(0)
Case "july"
wsQ.Cells(3, c).Value = "3Q " & vMonYr(0)
Case "october"
wsQ.Cells(3, c).Value = "4Q " & vMonYr(0)
End Select

For iRow = 5 To iLastRow
For iCol = 7 To 18 Step 6
wsQ.Cells(iRow, c).Value = wsQ.Cells(iRow, c).Value + wsQ.Cells(iRow, c + iCol - 1).Value
Next iCol
wsQ.Cells(iRow, c).Value = wsQ.Cells(iRow, c).Value / 3#
Next iRow


'delete 2nd and 3rd month columns
wsQ.Cells(5, c + 6).Resize(1, 12).EntireColumn.Delete

'merge the headings over 6 col
wsQ.Cells(3, c).Resize(1, 6).Merge
wsQ.Cells(3, c).Resize(1, 6).HorizontalAlignment = xlCenter
End Sub

kewiopex
11-20-2016, 03:03 PM
Paul
This coding is absolutely brilliant! Your skills are absolutely incredible!

I never thought that the issue could be solved in this way. I will be studying your code in detail, to learn some more but to as well adapt it to extend out the quarters for future quarters,

I believe, but please correct me, that I will need to modify the "case" and the "For iCol = 7 To 18 Step 6".

Thank you so much and for relieving my frustration.

Paul_Hossler
11-20-2016, 05:38 PM
IF YOUR DATA IS IN THE SAME FORMAT ...

You shouldn't need to do anything (famous last words)

1. The Split() above the Case takes an input like "2015 April" (or Jan, April, July, Oct since these start a quarter) and splits it at the space so that vMonYr(0) = "2015" and vMonYr(1) = "April"

The Case test is for a 'quarter-starting month' and makes the new title = (1/2/3/4)Q + the vMonYr(0) or "2Q 2015"



2. The "For iCol = 7 To 18 Step 6" just gets the second and third month values in the quarter (May and June) for each Metric (CN, etc.) and adds them to the first (April) and then divides by 3 to average.

Then it get the next row until all are done. Then it deletes the 12 columns for the 2nd and 3rd months

Then it starts 6 columns over (to skip the 6 metrics in this quarter) which should be the first column of the first month of the next quarter

It keeps doing that "Do While Len(.Cells(3, iMonCol).Value) > 0" until it hits an empty cell

So you should be able to add quarters at the end, and delete quarters from the beginning


Easiest way is to just single step through and see what each statement does and the values of key variables such as iCol

kewiopex
11-21-2016, 06:04 AM
Paul
That is too good to be true! I was well underway to understanding the coding but missed the imprtance of the statement that looks for the empty cell.
Cheers and many thanks again!