Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
FIND LETTER IN CELL (cond. form mult entries not wrking) | Excel Discussion (Misc queries) | |||
How can cell entries be based on word entries in another cell ? | Excel Worksheet Functions | |||
find the same entries and calculate sum | Excel Worksheet Functions | |||
how to find and add multiple entries | Excel Worksheet Functions | |||
Find duplicate entries | Excel Discussion (Misc queries) |