moving average of figures from separate tables

[Guest]

I have values for Jan to Dec for Year 1 in Table 1 and values for Jan to Dec
for Year 2 in Table 2.

I want to calculate averages in a 3rd table using source data from Tables 1
and 2.

Table 3 also has columns Jan to Dec for Year 2 but the value for each month
is based on values 6 months after the respective month and 6 months before
the month.

So in Table 3, column 1 under "Jan", I want the average value of Jul to Dec
in Year 1 and Jan to June in Year 2.

For column 2 "Feb" in Table 3, it would be the average value of Aug to Dec
in Year 1 and Jan to Jul in Year 2.

For column 7 "Jul" in Table 3, it would be the average value of Jan to Dec
in Year 2.

 

[Roger Govier]

Hi Melissa

It would make life so much simpler if you could hold the Year 1 and Year
2 data in a single table on Sheet 1.
Suppose you had Jan Year1 in cell B1 running through to Dec Year1 in M1,
then Jan Year2 in N1 etc.
You could hide columns B through M on Sheet1 if required.

Your formula then is simply in cell B2 of Sheet2
=AVERAGE(OFFSET(Sheet1!B2,0,6,1,12))

The offset is saying use a 0 row offset from the starting point (B2)
Start 6 columns away to the right
(You could equally make the reference point N2, which is Jan Year2, and
make the offset -6, which would be 6 columns to the left)
Make the range 1 row high
Take 12 columns worth of data

Adapt to suit your needs.

 

[Ken Johnson]

Hi Melissa,
If Table 1 is Jan - Dec in A2:A13, values in B2:B13,
Table 2 is Jan - Dec in D213, values in E2:E13,
Table 3 (For averages) is Jan - Dec in G2:G13 then average values, as
described, can be generated by typing =AVERAGE(B8:$B$13,E2:E7) into H2
then filling down to H8.

Does this help or confuse?

 

[Max]

One play to try ..

Sample construct available at:
http://cjoint.com/?bdkIrjLX6m
Melissa_misc.xls

In Sheet1,
Table 1 is in A1:L3, labels in A1:L1 (Jan, Feb .. Dec)
data within A2:L3

In Sheet2,
Table 2 is in A1:L3, labels in A1:L1 (Jan, Feb .. Dec)
data within A2:L3

In Sheet1,
Put in M1: =Sheet2!A1
Copy across to X1, fill down to X3
This will link / place Table 2 right next to Table 1
(makes it simpler to formulate the averages in Sheet3)

Then in Sheet3,
In A1:L1 are the labels: Jan, Feb ... Dec

Put in A2, array-enter (i.e. press CTRL+SHIFT+ENTER):
=AVERAGE(IF(Sheet1!G2:R2>0,Sheet1!G2:R2))
Copy A2 across and fill down to L3 to populate

Sheet3 will return the required "Table 3"

 

[Guest]

Thanks to Ken and Roger for your suggestions! I know having all in one table
would really make life easier but my tables 1 and 2 are actually in 2 excel
files. Is there no other way to have table 3 read from these 2?

Ken,
if I use your AVERAGE formula, I would get the wrong answer from Feb Year 2
onwards as there will be a "gap" between Dec Yr 1($B$13) and Jan Yr 2 (E2).

 

[Ken Johnson]

Hi Melissa,
Thought it was too easy to be true!
I'll try to stick to things other than Excel:-/
Ken Johnson

 

[Ken Johnson]

Hi Melissa,
=AVERAGE(B8:B$13,E$2:E7) is what I should have said.
Ken Johnson

 

[Roger Govier]

Hi Melissa

A simple construct like Max has shown would pull data from Table1 and
Table 2 to a new 4th Table.
Your values for Table 3 could then be obtained either using Max's array
formulae or, the Offset solution I posted.

If you really do want to use Sheet1 and Sheet2 then the horrible formula
is

=(IF(ISERROR(SUM(OFFSET(Sheet1!B2,0,6,1,8-COLUMN()))),0
,SUM(OFFSET(Sheet1!B2,0,6,1,8-COLUMN())))+
SUM(OFFSET(Sheet2!$B2,0,0,1,COLUMN()+4)))/12

as we need to take a decreasing number of columns from Sheet1 and an
increasing number of columns from Sheet2

 

[Guest]

Ah... but when you reach Jul Yr 2 (E8), you don't want Jan Yr 2 (E2) anymore!
;-)

 

[Guest]

Dear Roger,
thank you so much for the offset formula! A wee error threw me into
confusion at first (should be column()+5 and not 4 towards the end) but I
learnt lots and understood the formula much better during the troubleshooting.

 

[Guest]

Thanks, Max, for your suggestion but I think the Offset formula works best
for my needs.
Cheers!

 

[Max]

You're welcome, Melissa !
Thanks for posting back ..

 

[Roger Govier]

Hi Melissa
Thanks for the feedback. Glad it worked for you.
I don't quite understand why the offset should be column()+5 when
staring from column B, as this would give 6 months of data in the first
instance, when 6 months are being taken from the previous year
As column B is fixed in that part of the formula, it would progressively
take 7 though 12 as months from previous year are dropped.

However, if it is doing what you want, then fine.

 

[Ken Johnson]

Hi Melissa,
I noticed I was getting correct averages until the seventh one, so I
gave up (with my tail between my legs) and did some house-work.
Ken Johnson

 

[Guest]

Hi Roger,
you are right, it should be column()+4 if the starting cell is B.

Anyway, I realised that the formula won't work once I get to July of Year 2
because the starting cell of Year 2 is constrained with $B2.

In essence, I just want to take the average of 6 months before and after the
current month. So, perhaps I should be tackling the layout of the source
data instead to resolve this problem. I've created a new workbook containing
the info for all years in a single row and referenced my main file to this...
however, I'll get those "#value" errors if this new workbook is not opened
too. Sigh...

 

[Roger Govier]

Hi Melissa

The last formula I gave you could be modified to produce 12 months worth
of data, and will go beyond July of the second of the two years.
if we modify the second part of the formula to
SUM(OFFSET(Sheet2!$B2,0,MAX(0,COLUMN()-8),1,MIN(12,COLUMN()+4))))/12
(ignoring the divide by 12 bit)
This is starting at the fixed point of January ($B2) + 0 rows and
MAX(0,COLUMN()-8) columns away
When in column B, COLUMN()-8 would be -6, (so MAX would choose 0) and it
would start an offset of 0 columns away, so still column B.
As you drag across through columns C through M, when we get to August,
COLUMN()-8 would be 1, (so MAX would choose 1) and it would start 1
column tot he right, or column C.
The MIN (12,COLUMN()+4) part of the equation will prevent the formula
from taking more than 12 columns to sum, once the data goes beyond July
(column 8).

So the formula becomes
=(IF(ISERROR(SUM(OFFSET(Sheet1!B2,0,6,1,8-COLUMN()))),0
,SUM(OFFSET(Sheet1!B2,0,6,1,8-COLUMN())))+
SUM(OFFSET(Sheet2!$B2,0,MAX(0,COLUMN()-8),1,MIN(12,COLUMN()+4))))/12

This all pre-supposes that you have data on Sheet2 which extends beyond
December, into the 6 months of the succeeding year.

I still believe that my original solution of having the data for all
years on the same sheet is the best (and easiest to understand and
maintain) and it looks as though you are coming round to that view.
Why are you putting that data in another Workbook?
Why not put it on a Sheet within your Current Year's Workbook, (hidden
if necessary), then you would never have the problem of the book not
being open?
For this new sheet (let's call it Alldata), you would just copy in say
all of 2005's monthly figures from B through M.
In columns N through Y you would just need
=Sheet2!B2 copied across, to automatically pick up each month of the
current year as it was completed.
Then your average formula simply becomes
=AVERAGE(OFFSET(Alldata!B2,0,6,1,12))