Sum variable range of column entries in offset cell

Hi chaps,
Many thanks those of you who tried to help with this question last week, but
my simplified example wasn't good enough. I'll ask the question again but
with a more detailed explanantion. Sorry for any induced headaches in advance.

I have 900 lines of data (A3:AT902). Below this I am using an INDIRECT
formula to pull down (copy) certain lines from this mass of data (depending
on certain entries within the data) into 60 separate tables below. First
table (A915:AT943), second table (A951:AT979), etc. Much of the lines of data
copied down into my 60 tables is numeric, and on these I then need to perform
various sum, average, median and mode calculations (calcs on first table in
AV916:CO943).

So from my first table (A915:AT943), my first formulas (AV916:AV943) need to
find the sum of cells in column G one row above the formula cell and for a
specified number of cells (e.g. 6 cells) above this. The number of cells that
need including in the calculation is referenced from another worksheet (Admin
sheet!$O$22).

In the example below (using my real table cell references), value in 'Admin
sheet!'$O$22 is 6.

Formula in AV943 needs to sum the values in column G starting one row above
Row 943 (in G942) for 6 cells (G937:G942) (answer 18).

Formula in AV942 needs to sum the values in column G starting one row above
Row 942 (in G941) for 6 cells (G936:G941) (answer 19).

Etc.

If any cell in column G that is part of the calculation is blank, then the
formula in column AV needs to return a blank also.



A - G - AT - AV - CO

915 5
916 2 Formula
917 0 Formula
918 3 Formula
- -
936 2 Formula
937 3 Formula
938 5 Formula
939 1 Formula
940 1 Formula
941 7 Formula
942 1 Formula
943 4 Formula

Many thanks again for looking.

Some thoughts for these lines from your posting ..
Formula in AV943 needs to sum the values in column G starting one row above
Row 943 (in G942) for 6 cells (G937:G942) (answer 18).

Formula in AV942 needs to sum the values in column G starting one row above
Row 942 (in G941) for 6 cells (G936:G941) (answer 19).

Maybe something simple like this using OFFSET would satisfy ..

In AV942: =SUM(OFFSET(G941,,,-6))
This returns the sum of G936:G941, ie 6 cells counting from the OFFSET's
anchor G941 "up" (its "up" because of the negative "-6" used as the height
param).

Copy AV942 down 1 cell to AV943,
which increments the formula to: =SUM(OFFSET(G942,,,-6))
hence returning what you specify above (for AV943),
ie the sum of G937:G942

Any good? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---

Hi Max,
Thanks for that, adapted it slightly for my circumstances and that works
fine now.
Cheers

"Max" wrote:

Some thoughts for these lines from your posting ..
Formula in AV943 needs to sum the values in column G starting one row above
Row 943 (in G942) for 6 cells (G937:G942) (answer 18).

Formula in AV942 needs to sum the values in column G starting one row above
Row 942 (in G941) for 6 cells (G936:G941) (answer 19).

Maybe something simple like this using OFFSET would satisfy ..

In AV942: =SUM(OFFSET(G941,,,-6))
This returns the sum of G936:G941, ie 6 cells counting from the OFFSET's
anchor G941 "up" (its "up" because of the negative "-6" used as the height
param).

Copy AV942 down 1 cell to AV943,
which increments the formula to: =SUM(OFFSET(G942,,,-6))
hence returning what you specify above (for AV943),
ie the sum of G937:G942

Any good? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---