So I am trying to calculate an average for a golf score. I want the average to be of the persons 3 previous scores. So I am looking for a formula that will average the last 3 numbers in the column. I am open to any and all ideas. Attached is a screenshot of what I currently have.
3 Answers
try:
=AVERAGE(OFFSET(B2;
MAX(ARRAYFORMULA(ROW(B2:B17)*--(B2:B17<>"")))-4; ; 3))
again based on a false calculation where ROW
number gets multiplied by the cell that isn't empty B2:b17<>""
in order to find out where is the last entry in a given range B2:B17
. then ARRAYFORMULA
generates list of values which are filtered by MAX
to catch all errors (like 13 * x
) and then reverse OFFSET
is used from a starting point/cell B2 where is the beginning of the list, which is then restricted by last 3 values with -4, , 3
and passed into AVERAGE
formula.
as seen from the image, this solution takes the average from:
- player 1 numbers:
33, 22, 11
- player 2 numbers:
30, x, 20
- player 3 numbers:
9, 19, empty cell
try:
=AVERAGE(QUERY(SORT(B2:B17; ROW(B2:B17)*ISNUMBER(B2:B17); 0);
"select * limit 3"))
this is basically based on a false calculation where ROW
number gets multiplied by the number (ISNUMBER
) in the cell to find out where is the last number in a given range B2:B17
. an output is then mirrored with SORT
so last value becomes first from the top and then QUERY
takes it all and outputs only 3
rows/values that are passed into AVERAGE
as seen from the image, this solution takes the average from:
- player 1 numbers:
33, 22, 11
- player 2 numbers:
30, 20, 10
- player 3 numbers:
9, 19, 29
try:
=AVERAGE(INDEX(B2:B17; MATCH(9999; B2:B17)-2):
INDEX(B2:B17; MATCH(9999; B2:B17)))
this solution is based on a search for fake number 9999
in the range B2:B17
and unless that specific number is not provided, there is no MATCH
so 2 INDEX
lists are generated while one of them is slightly offset by two rows -2
which kind off negates all the values leaving only last 3 cells to be calculated by AVERAGE
formula.
as seen from the image, this solution takes the average from:
- player 1 numbers:
33, 22, 11
- player 2 numbers:
30, x, 20
- player 3 numbers:
9, 19, empty cell