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