Ok,

Original data is in sheet1, condensed data on sheet2...
Cell A1 on sheet2 contains a reference number carried forward from sheet1
Cell B1 on sheet2 contains the formula =AVERAGE(Sheet1!C2:C5)
Cell C1 on sheet2 contains the formula =AVERAGE(Sheet1!D2:D5)
Cell D1 on sheet2 contains the formula =AVERAGE(Sheet1!E2:E5)
and so on...
Cell B2 on sheet2 should have the formula =AVERAGE(Sheet1!C6:C9)
Cell C2 on sheet2 should have the formula =AVERAGE(Sheet1!D6:D9)
Cell D2 on sheet2 should have the formula =AVERAGE(Sheet1!E6:E9)
but actually reads (when cells in row 1 dragged to fill the cells below)...
Cell B2 on sheet2 sactually reads the formula =AVERAGE(Sheet1!C3:C6)
Cell C2 on sheet2 sactually reads the formula =AVERAGE(Sheet1!D3:D6)
Cell D2 on sheet2 sactually reads the formula =AVERAGE(Sheet1!E3:E6)

Ian

"Alan Beban" wrote in message
...
Post the failing formula.

Alan Beban

Garbin wrote:
"Garbin" wrote in message
...

Hi there,

I have a spreadsheet containing data representing a 3 dimensional data
structure in the following form;
Z1 Z2 Z3 Z4 ...
Y1 X1 X1 X1 X1
Y1 X2 X2 X2 X2
Y1 X3 X3 X3 X3
Y1 X4 X4 X4 X4
Y2 X1 X1 X1 X1
Y2 X2 X2 X2 X2
Y2 X3 X3 X3 X3
Y2 X4 X4 X4 X4
Y3 X1 X1 X1 X1
Y3 X2 X2 X2 X2
Y3 X3 X3 X3 X3
Y3 X4 X4 X4 X4
... ....
and so on
In other words for every value Y (of which there are 8800 in total) there
are four X values and I have one set of data (8800 x 4) per month (with
nine years of data in total), so 3,801,600 data items in total (8800 x 4
x 12 x 9)

What I would like to do is average the four X values for each value of Y
and re-display it on a new sheet something like...
Z1 Z2 Z3 Z4 ...
Y1 Xm Xm Xm Xm ...
Y2 Xm Xm Xm Xm ...
Y3 Xm Xm Xm Xm ...
Y4 Xm Xm Xm Xm ...
.... ... ... and so on

However, when I construct the first line of the new file and then try to


Sorry I mean 'new sheet' not 'new file'


drag it I don't get the right results i.e. if the first value of Xm is
given by AVERAGE(B1:B4) and the second is given AVERAGE(B5:B8), I
actually get AVERAGE(B2:B5). Even when I construct the first two lines
of the file and drag it I still don't get the right answer. How can I
achieve this simple action without having to resort to VBA? I'm sure I'm
missing something really obvious, so if I'm being a total noob please
tell me :-)

Thanks in advance
Ian