Hi chaps,

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

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