Find last 20 values in column

**Doggy** · 03-05-2010, 05:50 AM

Hi Guys,
I have a single column of numbers ~100 rows high.. Each week an extra number is added at the bottom of the list. Not all cells have values in them but I need to do calculations on the last 20 cells that do have values in them..

Thanks,
Doggy

**DonkeyOte** · 03-05-2010, 05:56 AM

A few ways, one might be:

=SUM(INDEX($A$1:$A$100,LARGE(INDEX(ISNUMBER($A$1:$A$100)*ROW($A$1:$A$100),0),MIN(20,COUNT($A$1:$A$100)))):$A$100)

Note: I'm curious that you say you have 100 rows but that new numbers are added each week... the implication is that the size of the range is not fixed.

**sweep** · 03-05-2010, 06:18 AM

Along similar lines:

=SUM(LOOKUP(LARGE(ISNUMBER(A1:A1000)*ROW(A1:A1000),{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20}),ROW(A1:A1000),A1:A1000))

**Doggy** · 03-05-2010, 06:38 AM

Thanks guys...
Just reading back over my post, I don't think I explained it very well..
The formula I need is for a golf handicapping system..
How it works is that each player's score 'over par' each week is put in the next cell down, eg, if a player scores 90 on a course rated at 72, then his score for that game is 18..(if a particular player doesn't play the cell is left blank.)
To calculate the player's handicap, his 20 most recent scores are taken, of which the 10 lowest scores are added together, multiplied by 0.96, divided by 10 and then the integer of that is his 'new' handicap..
So it's a 'rolling' handicap system..

I hope that makes a bit more sense..

Thanks again,
Doggy

**DonkeyOte** · 03-05-2010, 06:50 AM

If I've understood...

=INT((0.96*SUMPRODUCT(SMALL(INDEX($A$1:$A$100,LARGE(INDEX(ISNUMBER($A$1:$A$100)*ROW($A$1:$A$100),0), MIN(20,COUNT($A$1:$A$100)))):$A$100,ROW($1:$10))))/10)

Again the ranges will need to be modified I suspect - but try to keep as lean as possible.