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" wrote in message
...
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 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