1

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.

screen shot of sample data

3 Answers 3

0

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
0

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
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.