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

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

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

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

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

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:R20,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" wrote in message
...
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, 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:R20,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" wrote in message
...
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" wrote in message
...
Thanks, Max, for your suggestion but I think the Offset formula works best
for my needs.
Cheers!