How to increment an average formula by 6 columns in excel when copying and pasting

[kewiopex]

Hello Everyone
I have built an excel spreadsheet that takes monthly reading from 5 units for 6 variables , then averages each of the units for each variable to get a value for a quarter . The issue is that when expanding the spreadsheet by cut and pasting I need to manually adjust each of the monthly cell references in the formula to adjust for the shift of 6 columns. Is there an efficient way to copy and paste to get the offset automatically.

As an example, here is the monthly
[TABLE="width: 1393"]
<colgroup><col span="19"></colgroup><tbody>[TR]
[TD] <style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 11pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl129 { color: windowtext; font-size: 10pt; font-family: Arial,sans-serif; vertical-align: middle; }.xl130 { font-size: 16pt; font-weight: 700; font-family: Arial,sans-serif; text-align: center; vertical-align: middle; border-width: 0.5pt 0.5pt medium 1pt; border-style: solid solid none; border-color: windowtext windowtext -moz-use-text-color; }.xl131 { font-size: 16pt; font-weight: 700; font-family: Arial,sans-serif; text-align: center; vertical-align: middle; border-width: 1pt 0.5pt medium 1pt; border-style: solid solid none; border-color: windowtext windowtext -moz-use-text-color; }.xl132 { color: windowtext; font-size: 10pt; font-weight: 700; font-family: Arial,sans-serif; text-align: center; vertical-align: middle; border-width: 0.5pt 0.5pt medium 1pt; border-style: solid solid none; border-color: windowtext windowtext -moz-use-text-color; background: rgb(255, 255, 204) none repeat scroll 0% 0%; white-space: normal; }.xl133 { color: windowtext; font-size: 10pt; font-weight: 700; font-family: Arial,sans-serif; text-align: center; vertical-align: middle; border-width: 0.5pt 0.5pt medium; border-style: solid solid none; border-color: windowtext windowtext -moz-use-text-color; background: rgb(255, 255, 204) none repeat scroll 0% 0%; white-space: normal; }.xl134 { color: windowtext; font-size: 10pt; font-weight: 700; font-family: Arial,sans-serif; text-align: center; vertical-align: middle; border-width: 0.5pt 1pt medium 0.5pt; border-style: solid solid none; border-color: windowtext windowtext -moz-use-text-color; background: rgb(255, 255, 204) none repeat scroll 0% 0%; white-space: normal; }.xl135 { color: black; font-size: 16pt; font-weight: 700; font-family: Arial; text-align: center; vertical-align: middle; border-width: 1pt 0.5pt medium 1pt; border-style: solid solid none; border-color: windowtext windowtext -moz-use-text-color; background: rgb(146, 208, 80) none repeat scroll 0% 0%; }.xl136 { color: rgb(156, 0, 6); font-size: 16pt; font-weight: 700; font-family: Arial,sans-serif; text-align: center; vertical-align: middle; border-width: 0.5pt 0.5pt medium 1pt; border-style: solid solid none; border-color: windowtext windowtext -moz-use-text-color; background: rgb(146, 208, 80) none repeat scroll 0% 0%; }.xl137 { color: black; font-size: 16pt; font-weight: 700; font-family: Arial; text-align: center; vertical-align: middle; border-width: 0.5pt 0.5pt medium 1pt; border-style: solid solid none; border-color: windowtext windowtext -moz-use-text-color; background: rgb(146, 208, 80) none repeat scroll 0% 0%; }.xl138 { color: windowtext; font-size: 12pt; font-weight: 700; font-family: Arial,sans-serif; text-align: center; vertical-align: middle; border-width: 1pt medium 0.5pt 1pt; border-style: solid none solid solid; border-color: windowtext -moz-use-text-color windowtext windowtext; background: rgb(255, 255, 204) none repeat scroll 0% 0%; white-space: normal; }.xl139 { color: windowtext; font-size: 12pt; font-weight: 700; font-family: Arial,sans-serif; text-align: center; vertical-align: middle; border-width: 1pt medium 0.5pt; border-style: solid none; border-color: windowtext -moz-use-text-color; background: rgb(255, 255, 204) none repeat scroll 0% 0%; white-space: normal; }.xl140 { color: windowtext; font-size: 12pt; font-weight: 700; font-family: Arial,sans-serif; text-align: center; vertical-align: middle; border-width: 1pt 1pt 0.5pt medium; border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; background: rgb(255, 255, 204) none repeat scroll 0% 0%; white-space: normal; }</style> [TABLE="width: 1045"]
<colgroup><col style="width:55pt" width="55" span="19"> </colgroup><tbody>[TR]
[TD="class: xl129, width: 55"][/TD]
[TD="class: xl129, width: 55"] A
[/TD]
[TD="class: xl129, width: 55"] B
[/TD]
[TD="class: xl129, width: 55"]C
[/TD]
[TD="class: xl129, width: 55"]D
[/TD]
[TD="class: xl129, width: 55"]E
[/TD]
[TD="class: xl129, width: 55"] F
[/TD]
[TD="class: xl129, width: 55"] G H
[/TD]
[TD="class: xl129, width: 55"][/TD]
[TD="class: xl129, width: 55"]I
[/TD]
[TD="class: xl129, width: 55"]J
[/TD]
[TD="class: xl129, width: 55"]K
[/TD]
[TD="class: xl129, width: 55"]L
[/TD]
[TD="class: xl129, width: 55"]M
[/TD]
[TD="class: xl129, width: 55"]N
[/TD]
[TD="class: xl129, width: 55"]O
[/TD]
[TD="class: xl129, width: 55"]P
[/TD]
[TD="class: xl129, width: 55"]Q R
[/TD]
[TD="class: xl129, width: 55"][/TD]
[/TR]
[TR]
[TD="class: xl129"][/TD]
[TD="class: xl138, width: 330, colspan: 6"]2016 June[/TD]
[TD="class: xl138, width: 330, colspan: 6"]2016 July[/TD]
[TD="class: xl138, width: 330, colspan: 6"]2016 August[/TD]
[/TR]
[TR]
[TD="class: xl129, align: right"]1[/TD]
[TD="class: xl132, width: 55"]a[/TD]
[TD="class: xl133, width: 55"]b[/TD]
[TD="class: xl133, width: 55"]c[/TD]
[TD="class: xl133, width: 55"]d[/TD]
[TD="class: xl133, width: 55"]e[/TD]
[TD="class: xl134, width: 55"]f[/TD]
[TD="class: xl132, width: 55"]a
[/TD]
[TD="class: xl133, width: 55"]b
[/TD]
[TD="class: xl133, width: 55"]c
[/TD]
[TD="class: xl133, width: 55"]d
[/TD]
[TD="class: xl133, width: 55"]e
[/TD]
[TD="class: xl134, width: 55"]f
[/TD]
[TD="class: xl132, width: 55"]a
[/TD]
[TD="class: xl133, width: 55"]b
[/TD]
[TD="class: xl133, width: 55"]c
[/TD]
[TD="class: xl133, width: 55"]d
[/TD]
[TD="class: xl133, width: 55"]e
[/TD]
[TD="class: xl134, width: 55"]f
[/TD]
[/TR]
[TR]
[TD="class: xl129, align: right"]2[/TD]
[TD="class: xl131"]0.0[/TD]
[TD="class: xl131"]4.0[/TD]
[TD="class: xl135"]12.0[/TD]
[TD="class: xl131"]12.0[/TD]
[TD="class: xl131"]4.0[/TD]
[TD="class: xl131"]12.0[/TD]
[TD="class: xl131"]4.0[/TD]
[TD="class: xl131"]2.0[/TD]
[TD="class: xl131"] [/TD]
[TD="class: xl131"]12.0[/TD]
[TD="class: xl131"]0.0[/TD]
[TD="class: xl131"]12.0[/TD]
[TD="class: xl131"]9.0[/TD]
[TD="class: xl131"]4.0[/TD]
[TD="class: xl131"] [/TD]
[TD="class: xl131"]12.0[/TD]
[TD="class: xl131"]6.0[/TD]
[TD="class: xl131"]2.0[/TD]
[/TR]
[TR]
[TD="class: xl129, align: right"]3[/TD]
[TD="class: xl130"]8.0[/TD]
[TD="class: xl130"]10.0[/TD]
[TD="class: xl136"]10.0[/TD]
[TD="class: xl130"]9.0[/TD]
[TD="class: xl130"]10.0[/TD]
[TD="class: xl130"]10.0[/TD]
[TD="class: xl130"]10.0[/TD]
[TD="class: xl130"]0.0[/TD]
[TD="class: xl130"] [/TD]
[TD="class: xl130"]6.0[/TD]
[TD="class: xl130"]6.0[/TD]
[TD="class: xl130"]10.0[/TD]
[TD="class: xl130"]8.0[/TD]
[TD="class: xl130"]0.0[/TD]
[TD="class: xl130"] [/TD]
[TD="class: xl130"]6.0[/TD]
[TD="class: xl130"]10.0[/TD]
[TD="class: xl130"]9.0[/TD]
[/TR]
[TR]
[TD="class: xl129, align: right"]4[/TD]
[TD="class: xl130"]8.0[/TD]
[TD="class: xl130"]6.0[/TD]
[TD="class: xl137"]10.0[/TD]
[TD="class: xl130"]4.0[/TD]
[TD="class: xl130"]4.0[/TD]
[TD="class: xl130"]10.0[/TD]
[TD="class: xl130"]8.0[/TD]
[TD="class: xl130"]8.0[/TD]
[TD="class: xl130"] [/TD]
[TD="class: xl130"]7.0[/TD]
[TD="class: xl130"]4.0[/TD]
[TD="class: xl130"]10.0[/TD]
[TD="class: xl130"]8.0[/TD]
[TD="class: xl130"]8.0[/TD]
[TD="class: xl130"] [/TD]
[TD="class: xl130"]4.0[/TD]
[TD="class: xl130"]4.0[/TD]
[TD="class: xl130"]10.0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 6"][/TD]
[TD="colspan: 6"][/TD]
[TD="colspan: 6"][/TD]
[/TR]
[TR]
[TD]Quarterly formula for a above would be
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Avg(A2,G2,M2), and for b the average would be Avg(B2,H2, N2)
And if I now copy and paste, to create a quarterly for the October, November, December, the avg formula would not shift by the 6 columns when pasting the average formula
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[PaddyD]

Hi.

Is it too late to adjust your data deign? As you've discovered, excel doesn't like how you've set things out.

The following would be 'better' in some sense:

Date | Unit | Value

...in columns. You could then use pivot tables, sumif()s etc as they were designed.

If it is too late, let us know.

 

[kewiopex]

Thank you for the quick response. Unfortunately, I have inherited this design at a corporate level. I did not show it but all values are conditionally formatted to show all levels of performance. I am stuck with the format as is. Sounds like I may have to to continue to manually adjust the avg formula. I have seen an offset but it looks like it will not be able to be adapted.

 

[PaddyD]

Shame.

Can you precisely specify which columns the average() formulas need to go into?

 

[kewiopex]

Paddy
Thank you once again for looking art this.

The average formula is on another worksheet called Quarterly, and has an arrangement such that for each entity the average would be calculated. As an example for
<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'; color: #000000}</style>=AVERAGE(Monthly!A2,Monthly!G2,Monthly!M2). The issue is that I would like to be able to copy and paste the average formulas to each quarter that adjusts for the offset location.
Hope this helps to specify.

[TABLE="class: cms_table, width: 1045"]
<tbody>[TR]
[TD="class: cms_table_xl129, width: 55"]A[/TD]
[TD="class: cms_table_xl129, width: 55"]B[/TD]
[TD="class: cms_table_xl129, width: 55"]C[/TD]
[TD="class: cms_table_xl129, width: 55"]D[/TD]
[TD="class: cms_table_xl129, width: 55"]E[/TD]
[TD="class: cms_table_xl129, width: 55"]F[/TD]
[TD="class: cms_table_xl129, width: 55"]G [/TD]
[TD="class: cms_table_xl129, width: 55"][/TD]
[TD="class: cms_table_xl129, width: 55"]I[/TD]
[TD="class: cms_table_xl129, width: 55"]J[/TD]
[TD="class: cms_table_xl129, width: 55"]K[/TD]
[TD="class: cms_table_xl129, width: 55"]L[/TD]
[TD="class: cms_table_xl129, width: 55"]M[/TD]
[TD="class: cms_table_xl129, width: 55"]N[/TD]
[TD="class: cms_table_xl129, width: 55"]O[/TD]
[TD="class: cms_table_xl129, width: 55"]P[/TD]
[TD="class: cms_table_xl129, width: 55"]Q R[/TD]
[TD="class: cms_table_xl129, width: 55"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl129"][/TD]
[TD="class: cms_table_xl138, width: 330, colspan: 6"]2016 Q3[/TD]
[TD="class: cms_table_xl138, width: 330, colspan: 6"]2016 Q4[/TD]
[TD="class: cms_table_xl138, width: 330, colspan: 6"]2017 Q1[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl129, align: right"]1[/TD]
[TD="class: cms_table_xl132, width: 55"]a[/TD]
[TD="class: cms_table_xl133, width: 55"]b[/TD]
[TD="class: cms_table_xl133, width: 55"]c[/TD]
[TD="class: cms_table_xl133, width: 55"]d[/TD]
[TD="class: cms_table_xl133, width: 55"]e[/TD]
[TD="class: cms_table_xl134, width: 55"]f[/TD]
[TD="class: cms_table_xl132, width: 55"]a[/TD]
[TD="class: cms_table_xl133, width: 55"]b[/TD]
[TD="class: cms_table_xl133, width: 55"]c[/TD]
[TD="class: cms_table_xl133, width: 55"]d[/TD]
[TD="class: cms_table_xl133, width: 55"]e[/TD]
[TD="class: cms_table_xl134, width: 55"]f[/TD]
[TD="class: cms_table_xl132, width: 55"]a[/TD]
[TD="class: cms_table_xl133, width: 55"]b[/TD]
[TD="class: cms_table_xl133, width: 55"]c[/TD]
[TD="class: cms_table_xl133, width: 55"]d[/TD]
[TD="class: cms_table_xl133, width: 55"]e[/TD]
[TD="class: cms_table_xl134, width: 55"]f[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl129, align: right"]2[/TD]
[TD="class: cms_table_xl131"]0.0[/TD]
[TD="class: cms_table_xl131"]4.0[/TD]
[TD="class: cms_table_xl135"]12.0[/TD]
[TD="class: cms_table_xl131"]12.0[/TD]
[TD="class: cms_table_xl131"]4.0[/TD]
[TD="class: cms_table_xl131"]12.0[/TD]
[TD="class: cms_table_xl131"]4.0[/TD]
[TD="class: cms_table_xl131"]2.0[/TD]
[TD="class: cms_table_xl131"][/TD]
[TD="class: cms_table_xl131"]12.0[/TD]
[TD="class: cms_table_xl131"]0.0[/TD]
[TD="class: cms_table_xl131"]12.0[/TD]
[TD="class: cms_table_xl131"]9.0[/TD]
[TD="class: cms_table_xl131"]4.0[/TD]
[TD="class: cms_table_xl131"][/TD]
[TD="class: cms_table_xl131"]12.0[/TD]
[TD="class: cms_table_xl131"]6.0[/TD]
[TD="class: cms_table_xl131"]2.0[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl129, align: right"]3[/TD]
[TD="class: cms_table_xl130"]8.0[/TD]
[TD="class: cms_table_xl130"]10.0[/TD]
[TD="class: cms_table_xl136"]10.0[/TD]
[TD="class: cms_table_xl130"]9.0[/TD]
[TD="class: cms_table_xl130"]10.0[/TD]
[TD="class: cms_table_xl130"]10.0[/TD]
[TD="class: cms_table_xl130"]10.0[/TD]
[TD="class: cms_table_xl130"]0.0[/TD]
[TD="class: cms_table_xl130"][/TD]
[TD="class: cms_table_xl130"]6.0[/TD]
[TD="class: cms_table_xl130"]6.0[/TD]
[TD="class: cms_table_xl130"]10.0[/TD]
[TD="class: cms_table_xl130"]8.0[/TD]
[TD="class: cms_table_xl130"]0.0[/TD]
[TD="class: cms_table_xl130"][/TD]
[TD="class: cms_table_xl130"]6.0[/TD]
[TD="class: cms_table_xl130"]10.0[/TD]
[TD="class: cms_table_xl130"]9.0[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl129, align: right"]4[/TD]
[TD="class: cms_table_xl130"]8.0[/TD]
[TD="class: cms_table_xl130"]6.0[/TD]
[TD="class: cms_table_xl137"]10.0[/TD]
[TD="class: cms_table_xl130"]4.0[/TD]
[TD="class: cms_table_xl130"]4.0[/TD]
[TD="class: cms_table_xl130"]10.0[/TD]
[TD="class: cms_table_xl130"]8.0[/TD]
[TD="class: cms_table_xl130"]8.0[/TD]
[TD="class: cms_table_xl130"][/TD]
[TD="class: cms_table_xl130"]7.0[/TD]
[TD="class: cms_table_xl130"]4.0[/TD]
[TD="class: cms_table_xl130"]10.0[/TD]
[TD="class: cms_table_xl130"]8.0[/TD]
[TD="class: cms_table_xl130"]8.0[/TD]
[TD="class: cms_table_xl130"][/TD]
[TD="class: cms_table_xl130"]4.0[/TD]
[TD="class: cms_table_xl130"]4.0[/TD]
[TD="class: cms_table_xl130"]10.0[/TD]
[/TR]
</tbody>[/TABLE]

 

[PaddyD]

Hi,

Sorry it's been a few days - one of the reasons I don't post so much these days is i get too busy and don't want to leave people hanging. Anyway...

You didn't answer the question I asked above - which column are these calculations entered in?. You'll see why this is important when we get there.

To get a formula to do what you're after, we're going to exploit regularities in your data structure. Each block of data is 6 columns wide and your data starts in column 1. So each quarter's block of data is 18 columns wide. So the trick is to get a formula that will average cells in col 1,7, 13 in the first case, then 19,25,31 in the second quarter etc.

Assuming the first average() formula is entered in column 1:

=AVERAGE(INDEX($A$3:$AZ$3,1,(1+(18*(COLUMN()-1)))),INDEX($A$3:$AZ$3,1,(7+(18*(COLUMN()-1)))),INDEX($A$3:$AZ$3,1,(13+(18*(COLUMN()-1)))))

…it'll adjust as required when entered in column 2. If the initial destination column isn't 1, you will need to adjust the column()-1 component accordingly.

You'll need to make sure the details match to your situation re index ranges etc, but hopefully you can work it out from here. Still, let us know if you can't get it working...

 

[kewiopex]

Thank you so much PaddyD. I would never have figured this one out myself. And I had not worked with index. But I see its effctiveness. So I have gained greatly from this .I will let you know if it does not work. I will need to adjust as you mentioned and specify cell references since the monthly data is in another worksheet called monthly.

 

[kewiopex]

Good day PaddyD
I just tried the function but unfortunately, I was not successful. After reviewing the issue, I noted that because of the periodicity of the data, it would not work as intended. You were ru=ight about the category a repeating at column 19 however the function would not work as intended. For example for Quarter 2, category a would be captured at 108 plus 1 from the function while in the actual table for the monthly it would be at column 19. I did amend the function and it worked perfectly in the other project I had to do.
Any suggestion? Was the explanation OK?