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

L

Leroy 694

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

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?
 
T

Toppers

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
 
T

Toppers

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

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

Enter with Ctrl+Shift+Enter
 
J

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..
 
T

Toppers

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)))
 
T

Toppers

... 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))))
 
R

robert morris

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top