Sum variable range of column entries in offset cell

[Struggling in Sheffield]

Hi chaps,

I’m trying to look up a column of values from an offset cell and sum the
column values (or depending on formula used, work out the average, median or
mode of the data range in the column).
The hard bit is I need to specify (using a variable number value in another
cell) how many cells up from the bottom of the column are included in my
calculations.

Here’s a simplified example to (hopefully) try and explain more clearly:

A B C D E F G
1 5
2 2 5
3 0
4 3
5 1
6 7
7 1
8 4
9 Formula


B1:B8 contain the values I want to use in my calculations.
G2 contains the (variable) value that tells me how many cells from B8
upwards I want in my calculation.
F9 contains the formula.

In the example, Formula (F9) needs to SUM B4:B8 (last 5 entries in the
column).
Depending on my needs the formula could also either be Average, Median or
Mode of the number of cells specified.

Sum = 16
Average = 3.2
Median = 3
Mode = 1

Hope my explanation is clear enough and you can help.
Many thanks for looking,
Steve.

 

[Bob Umlas]

Ctrl/Shift/Enter:
=SUM(TRANSPOSE(OFFSET(B1,COUNT(B:B)-G2,0,G2,1)))
Bob Umlas
Excel MVP

 

[Teethless mama]

=SUM(OFFSET(B1,COUNT(B:B),,-G2-1,))

 

[T. Valko]

Assuming there are no empty cells *within* the range and there is no other
unrelated numeric data further down the column.

=FUNCTION(INDEX(B:B,COUNT(B:B)):INDEX(B:B,ABS(G2-1-COUNT(B:B))))

Replace FUNCTION with the appropriate function: SUM, AVERAGE, MODE, etc.

 

[Ron Rosenfeld]

Hi chaps,

I’m trying to look up a column of values from an offset cell and sum the
column values (or depending on formula used, work out the average, median or
mode of the data range in the column).
The hard bit is I need to specify (using a variable number value in another
cell) how many cells up from the bottom of the column are included in my
calculations.

Here’s a simplified example to (hopefully) try and explain more clearly:

A B C D E F G
1 5
2 2 5
3 0
4 3
5 1
6 7
7 1
8 4
9 Formula


B1:B8 contain the values I want to use in my calculations.
G2 contains the (variable) value that tells me how many cells from B8
upwards I want in my calculation.
F9 contains the formula.

In the example, Formula (F9) needs to SUM B4:B8 (last 5 entries in the
column).
Depending on my needs the formula could also either be Average, Median or
Mode of the number of cells specified.

Sum = 16
Average = 3.2
Median = 3
Mode = 1

Hope my explanation is clear enough and you can help.
Many thanks for looking,
Steve.

With no blank cells:

=SUM(OFFSET($B$1,COUNT(B:B)-1,0,-$G$2,1))
--ron

 

[Struggling in Sheffield]

Hi again chaps,
Sorry for the delayed response, have been away for a few days.

The situation is not as straightforward as my original post. Will attempt a
more comprehensive explanation which may invoke a headache: -

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
performed in AV916:CO943).

So from my first table (A915:AT943) my first calculations (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.