moving average of figures from separate tables

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.

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.

--
Regards

Roger Govier



Melissa <[email protected]> wrote:
> 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.

Hi Melissa,
If Table 1 is Jan - Dec in A2:A13, values in B2:B13,
Table 2 is Jan - Dec in D2:D13, 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?

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"
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Melissa" <[email protected]> wrote in message
news:[email protected]...
> 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.
>

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" wrote:

> Hi Melissa,
> If Table 1 is Jan - Dec in A2:A13, values in B2:B13,
> Table 2 is Jan - Dec in D2:D13, 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?
>
>

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

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

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

--
Regards

Roger Govier



Melissa <[email protected]> wrote:
> 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" wrote:
>
>> Hi Melissa,
>> If Table 1 is Jan - Dec in A2:A13, values in B2:B13,
>> Table 2 is Jan - Dec in D2:D13, 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?

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

"Ken Johnson" wrote:

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

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.


"Roger Govier" wrote:

> 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
>
> --
> Regards
>
> Roger Govier
>
>
>
> Melissa <[email protected]> wrote:
> > 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" wrote:
> >
> >> Hi Melissa,
> >> If Table 1 is Jan - Dec in A2:A13, values in B2:B13,
> >> Table 2 is Jan - Dec in D2:D13, 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?
>
>
>

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

"Max" wrote:

> 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"
> --
> Rgds
> Max
> xl 97
> ---
> Singapore, GMT+8
> xdemechanik
> http://savefile.com/projects/236895
> --
> "Melissa" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> >
>
>
>

You're welcome, Melissa !
Thanks for posting back ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Melissa" <[email protected]> wrote in message
news:[email protected]...
> Thanks, Max, for your suggestion but I think the Offset formula works best
> for my needs.
> Cheers!

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.

--
Regards

Roger Govier

Melissa <[email protected]> wrote
> 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. "Roger Govier" wrote:
>> 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
>> --
>> Regards
>> Roger Govier
>> Melissa <[email protected]> wrote:
>>> 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" wrote:
>>>> Hi Melissa,
>>>> If Table 1 is Jan - Dec in A2:A13, values in B2:B13,
>>>> Table 2 is Jan - Dec in D2:D13, 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?

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

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" wrote:

> 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.
>
> --
> Regards
>
> Roger Govier
>
> Melissa <[email protected]> wrote
> > 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. "Roger Govier" wrote:
> >> 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

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))


--
Regards

Roger Govier


"Melissa" <[email protected]> wrote in message
news:[email protected]...
> 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" wrote:
>
>> 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.
>>
>> --
>> Regards
>>
>> Roger Govier
>>
>> Melissa <[email protected]> wrote
>> > 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. "Roger Govier" wrote:
>> >> 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
>