Find the last 4 cell entries in a row
 

I have a worksheet where I input sporting scores for a year. If a player
plays 12 games, he/she will have 12 scores entered on a row. They may miss a
game and have no score entered as below.
G1 G2 G3 G4 G5 G6 G7 G8 G9 G10 G11 G12
20 18 22 30 28 16 17 23 14

What I am trying to do is get the scores of the last 4 games they played.
From the example above the scores would be 16, 17, 23 & 14 from games 8. 9,
10 & 12. I want to then average their best two scores from the last 4 games.

Find the last 4 cell entries in a row
 

With scores in A2:L2 try the below. Please note that this is an array
formula. You create array formulas in the same way that you create other
formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If
successful in 'Formula Bar' you can notice the curly braces at both ends like
"{=<formula}"

=AVERAGE(LARGE(LOOKUP(LARGE(IF(ISNUMBER(A2:L2),COL UMN(A2:L2)),
ROW(1:4)),COLUMN(A2:L2),A2:L2),{1,2}))

If this post helps click Yes
---------------
Jacob Skaria


"Johno67" wrote:

I have a worksheet where I input sporting scores for a year. If a player
plays 12 games, he/she will have 12 scores entered on a row. They may miss a
game and have no score entered as below.
G1 G2 G3 G4 G5 G6 G7 G8 G9 G10 G11 G12
20 18 22 30 28 16 17 23 14

What I am trying to do is get the scores of the last 4 games they played.
From the example above the scores would be 16, 17, 23 & 14 from games 8. 9,
10 & 12. I want to then average their best two scores from the last 4 games.

Find the last 4 cell entries in a row
 

Thankyou. I changed the first LARGE to SMALL and it did exactly what I was
after. I was after the lowest score of the last 4.

"Jacob Skaria" wrote:

With scores in A2:L2 try the below. Please note that this is an array
formula. You create array formulas in the same way that you create other
formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If
successful in 'Formula Bar' you can notice the curly braces at both ends like
"{=<formula}"

=AVERAGE(LARGE(LOOKUP(LARGE(IF(ISNUMBER(A2:L2),COL UMN(A2:L2)),
ROW(1:4)),COLUMN(A2:L2),A2:L2),{1,2}))

If this post helps click Yes
---------------
Jacob Skaria


"Johno67" wrote:

I have a worksheet where I input sporting scores for a year. If a player
plays 12 games, he/she will have 12 scores entered on a row. They may miss a
game and have no score entered as below.
G1 G2 G3 G4 G5 G6 G7 G8 G9 G10 G11 G12
20 18 22 30 28 16 17 23 14

What I am trying to do is get the scores of the last 4 games they played.
From the example above the scores would be 16, 17, 23 & 14 from games 8. 9,
10 & 12. I want to then average their best two scores from the last 4 games.