Sum(small excluding text

[mheals]

I'm doing a masters pool and have the following line for each entry

<table border="0" cellpadding="0" cellspacing="0" width="2162"><col width="112"><col width="31"><col width="97"><col width="32"><col width="118"><col width="34"><col width="101"><col width="32"><col width="109"><col width="30"><col width="136"><col width="26"><col width="115"><col width="29"><col width="115"><col width="29"><col width="115"><col width="32"><col width="115"><col width="30"><col width="106"><col width="33"><col width="127"><col width="32"><col width="106"><col width="33"><col width="106"><col width="31"><col width="116"><col width="34"><tr height="20"> <td class="xl63" style="height: 15pt; width: 84pt;" width="112" height="20">Martin Kaymer</td> <td class="xl64" style="width: 23pt;" width="31">6</td> <td class="xl63" style="width: 73pt;" width="97">Rory Mcilroy</td> <td class="xl65" style="width: 24pt;" width="32">-7</td> <td class="xl63" style="width: 89pt;" width="118">Dustin Johnson</td> <td class="xl64" style="width: 26pt;" width="34">2</td> <td class="xl63" style="width: 76pt;" width="101">Bubba Watson</td> <td class="xl65" style="width: 24pt;" width="32">2</td> <td class="xl63" style="width: 82pt;" width="109">Hunter Mahan</td> <td class="xl65" style="width: 23pt;" width="30">3</td> <td class="xl63" style="width: 102pt;" width="136">Martin Laird</td> <td class="xl64" style="width: 20pt;" width="26">2</td> <td class="xl63" style="width: 86pt;" width="115">Yong-Eun Yang</td> <td class="xl65" style="width: 22pt;" width="29">-8</td> <td class="xl63" style="width: 86pt;" width="115">Padraig Harrington</td> <td class="xl65" style="width: 22pt;" width="29">5</td> <td class="xl63" style="width: 86pt;" width="115">K.J. Choi</td> <td class="xl65" style="width: 24pt;" width="32">-6</td> <td class="xl63" style="width: 86pt;" width="115">Mark Wilson</td> <td class="xl64" style="width: 23pt;" width="30">4</td> <td class="xl63" style="width: 80pt;" width="106">Angel Cabrera</td> <td class="xl65" style="width: 25pt;" width="33">-2</td> <td class="xl63" style="width: 95pt;" width="127">Stephen Marino</td> <td class="xl65" style="width: 24pt;" width="32">1</td> <td class="xl63" style="width: 80pt;" width="106">Jeffrey Overton</td> <td class="xl65" style="width: 25pt;" width="33">1</td> <td class="xl63" style="width: 80pt;" width="106">Stewart Cink</td> <td class="xl65" style="width: 23pt;" width="31">2</td> <td class="xl63" style="width: 87pt;" width="116">Jhonattan Vegas</td> <td class="xl66" style="width: 26pt;" width="34">0</td> </tr></table>
so in one cell has the golfer's name, in the next his score and so on. What I want to do is get the sum of the 5 smallest numbers accross each row for each participant in the pool. When I try an array formula I get a !#NUM error which I am assuming is because of the text. I would appreciate any help with a formula that would get me the sum of the 5 smallest scores. Thank you.

 

[T. Valko]

I just got a blank cell. I should note that the range of data is (B2:AE2) as each golfer and score are in separate cells.

In the formula I suggested did you replace the references to A1:J1 with the actual references B2:AE2?

 

[T. Valko]

I get 0

Is that a reply to my question:

OK, what result do you get with this formula:

=COUNT(B2:AE2)

This is why you should quote the reply you're responding to.

 

[mheals]

I did adjust the range to suit the formula originally, and it came up blank.

 

[mheals]

Is that a reply to my question:


This is why you should quote the reply you're responding to.

Sorry, I am new to this. To clarify, I got a blank cell after I adjusted the range to the first formula suggested.

I also received a 0 value for the formula of =COUNT(B2:AE2)

 

[T. Valko]

I did adjust the range to suit the formula originally, and it came up blank.

OK, it sounds to me like the numbers in the range aren't real numeric numbers. In Excel there are numeric numbers and TEXT numbers.

TEXT numbers screw things up as you're experiencing!

See if this helps...

One way to convert TEXT numbers to numeric numbers...

Copy an empty unused cell.
Select the empty cell
Right click>Copy

Now select the cells in the range B2:AE2 that contain the numbers
Right click>Paste Special>Add>OK

Did that make any difference?

 

[mheals]

OK, it sounds to me like the numbers in the range aren't real numeric numbers. In Excel there are numeric numbers and TEXT numbers.

TEXT numbers screw things up as you're experiencing!

See if this helps...

One way to convert TEXT numbers to numeric numbers...

Copy an empty unused cell.
Select the empty cell
Right click>Copy

Now select the cells in the range B2:AE2 that contain the numbers
Right click>Paste Special>Add>OK

Did that make any difference?

That worked Perfect! Thank you all so much for your help!!!

 

[T. Valko]

That worked Perfect! Thank you all so much for your help!!!

Good deal! Thanks for the feedback!