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

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.

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

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?

--
Biff
Microsoft Excel MVP


"Leroy 694" <Leroy wrote in message
...
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.

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

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

"Leroy 694" wrote:

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.

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

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

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

Enter with Ctrl+Shift+Enter

"Toppers" wrote:

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

"Leroy 694" wrote:

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.

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

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


"Toppers" wrote in message
...
.....if you want sum ....

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

Enter with Ctrl+Shift+Enter

"Toppers" wrote:

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

"Leroy 694" wrote:

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.

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

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

CSE required he

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

"Jim May" wrote:

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


"Toppers" wrote in message
...
.....if you want sum ....

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

Enter with Ctrl+Shift+Enter

"Toppers" wrote:

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

"Leroy 694" wrote:

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.

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

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


"Jim May" wrote:

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


"Toppers" wrote in message
...
.....if you want sum ....

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

Enter with Ctrl+Shift+Enter

"Toppers" wrote:

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

"Leroy 694" wrote:

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.

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

I have been using this formula perfectly for some time.

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

Bob M.

"Leroy 694" wrote:

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.