how do I calculate the total of the lowest 10 numbers in an array

[Guest]

of 20.
I am trying to create a formula/macro for calculating my golf handicap in
Excel. I need a formula that will select the lowest 10 scores of the latest
20 posted.

 

[T. Valko]

You need to provide some details:

Will there *always* be 20 scores?
If there aren't 20 scores what do you want to do?
Where are these scores? Do they go down a column or across a row?
Are there any empty cells in your range of scores?

 

[Guest]

Try:

=INT(SMALL($A$1:$A$20+ROW()*0.0001,ROW(1:10)))

Enter with Ctrl+Shift+Enter and copy down for 10 rows.

HTH

 

[Guest]

......if you want sum ....

=SUM(INT(SMALL($A$1:$A$20+ROW()*0.0001,ROW(1:10))))

Enter with Ctrl+Shift+Enter

 

[Jim May]

FWIW:
I got the same result with:

=SMALL($A$2:$A$21,ROW(1:1)) in B2 copied down to B11

without being CSE entered..

 

[Guest]

So did I ... BUT convinced it didn't when I tried earlier! (vbg).

CSE required here:

=SUM(SMALL($A$2:$A$21,ROW(1:10)))

 

[Guest]

... so did I but I am sure it didn't work when I first tried (but I guess we
always say that!). And I added the + row()*0.001 because it didn't list the
duplicates I had in my data .. again this apears not to be necessary. So not
sure what I did differently ..???

this does require CSE:

=SUM(INT(SMALL($A$1:$A$20,ROW(1:10))))

 

[Guest]

I have been using this formula perfectly for some time.

=IF(COUNT(F4:Y4)=0,"",IF(COUNT(F4:Y4)<10,AVERAGE(F4:Y4),AVERAGE(SMALL(F4:Y4,ROW(INDIRECT("1:10"))))))-72
Enter with Control+Shift+Enter

Bob M.