Yet Another Excel Question

[CubicZirconia]

Basically I need to average the lowest ten of the latest 20 numbers in a column.

handicap

The boxed numbers represent handicap differentials. As I play more rounds of golf, I will add new rows beneath the box. Even If I had 100 rows, only the latest 20 (lowest down on the spreadsheet) count towards the handicap. I need the lowest 10 (as in -1 is lower than 5 or 6) of those latest 20 averaged. Can anyone help me out. If you need something clarified let me know.


Notes: I don't know Visual Basic (or anything else like it) and my search function doesn't work, so forgive me if this has been asked before.

 

[petesamprs]

Assuming you are entering each round played in order (descending), then you can set up a formula that only incorporates the last 20.

First, I'd add an adjacent column next to your handicaps that "ranks" the last 20. the function is =Rank() and it will display a # next to each handicap saying where it ranks in the range you have defined in the function. Since you only want the rank of the most recent 20 round, just add an if statement that tests to see if the round is one of the last 20. The whole column's formula should look like this: =IF(round>$final round$-20,RANK(handicap,$list of handicaps$,1),"") --- the "1" ranks the list so smaller numbers are ranked higher

Then at the bottom of the handicap column, just do a sumif() / countif() to get the average. =SUMIF($range of rankings$,"<=10",$range of handicaps$)/COUNTIF($range of rankings$,"<=10")

Everytime you add a new round, however, you'll have to quickly adjust your rank formula to i) shift down your handicap range (ie, remove the oldest and add the newest round) and ii) move down your "final round" cell to the newest round. Shift down the ranges on the sumif and countif formulas as well.

A more elegant solution is possible - but too busy to think of one currently.

 

[CubicZirconia]

Originally posted by: petesamprs
Assuming you are entering each round played in order (descending), then you can set up a formula that only incorporates the last 20.

First, I'd add an adjacent column next to your handicaps that "ranks" the last 20. the function is =Rank() and it will display a # next to each handicap saying where it ranks in the range you have defined in the function. Since you only want the rank of the most recent 20 round, just add an if statement that tests to see if the round is one of the last 20. The whole column's formula should look like this: =IF(round>$final round$-20,RANK(handicap,$list of handicaps$,1),"") --- the "1" ranks the list so smaller numbers are ranked higher

Then at the bottom of the handicap column, just do a sumif() / countif() to get the average. =SUMIF($range of rankings$,"<=10",$range of handicaps$)/COUNTIF($range of rankings$,"<=10")

Everytime you add a new round, however, you'll have to quickly adjust your rank formula to i) shift down your handicap range (ie, remove the oldest and add the newest round) and ii) move down your "final round" cell to the newest round. Shift down the ranges on the sumif and countif formulas as well.

A more elegant solution is possible - but too busy to think of one currently.

Well thanks for the info. I'll give that a try.

 

[AtTheGates]

SMALL and LARGE might also work. If you have a column that is the date you played you can use LARGE(dateplayed,20). Assume this is in cell C1. Then use SMALL(if(dateplayed<=$C$1,score),10) (call this D1). This give you your 10th lowest score of the past 20 games. Now you can get the average with SUM(if(dateplayed<=$C$1,if(score<=$D$1,score)))/10. The last two are array formulas. You have to type in the formula and then hit ctrl+shift+enter. If you don't know about these lookup it up because they are very useful. dateplayed and score are ranges. good luck