Find last 20 values in column

**Doggy** · 03-05-2010, 06: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, 06:56 AM

A few ways, one might be:

Please Login or Register  to view this content.

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, 07:18 AM

Along similar lines:

Please Login or Register  to view this content.

**Doggy** · 03-05-2010, 07: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, 07: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.

**Doggy** · 03-05-2010, 08:20 AM

Thanks Donkey, exactly what I was looking for!

Thanks to you too Sweep!

Doggy

**Vercogen** · 10-10-2012, 05:24 PM

Hi

Thanks, this was exactly what i was looking for, but want to take it one step further.

I want to highlight the 20 most recent scores blue and highlight the 10 lowest scores of that 20 scores red.

I did play around with the conditioning, but could not make it work.

Thanks in advance

Also: taking the above formula, i want to to calculate the player's handicap, his 13 most recent scores are taken, of which the 6 lowest scores are added together, multiplied by 0.96, divided by 6.

I did try
=INT((0.96*SUMPRODUCT(SMALL(INDEX($A$1:$A$100,LARGE(INDEX(ISNUMBER($A$1:$A$100)*ROW($A$1:$A$100),0),MIN(13,COUNT($A$1:$A$100)))):$A$100,ROW($1:$6))))/6)

but it don't work.

**Vercogen** · 10-11-2012, 01:39 PM

B
Bump
m
p

**arlu1201** · 10-11-2012, 02:20 PM

Vercogen,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.