Sum variable range of column entries in offset cell

[Struggling in Sheffield]

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.

 

[Max]

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

 

[Struggling in Sheffield]

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