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?