# How to get average from last 3 values?

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