FORMULA to COUNT LOWER 2 OF 3 VALUES

**timmy5712** · 08-03-2007, 09:14 PM

Need some help with a formula please.

Keeping golf scores for a list of players. Column A = Name; B = 1st round score; C = 2nd round; D = 3rd round score; E = TOTAL of best 2 rounds.

e.g. A= John Doe B=75 C=72 D=80. I’m looking for a formula that excludes the 80 score, & sums the rest. In the example E= 147.

A couple of events that need to be catered for:
# If a player does not play one of the 3 rounds, cd I record 0(zero) as the value for that round and get the formula to ignore all zero values?
# If 2 rounds (or even 3) had the same score, the formula wd need to cover that event..
e.g. 70,70,77 = 140. and 72,75,75 = 147

**Paul** · 08-03-2007, 09:31 PM

Here's a quick solution, although I'm sure there's a fancier way to do it:

=IF(SMALL(B1:D1,1)=0,SUM(SMALL(B1:D1,2),SMALL(B1:D1,3)),SUM(SMALL(B1:D1,1),SMALL(B1:D1,2)))

**FrankBoston** · 08-03-2007, 10:46 PM

Starting with PJoaqin's work, here is my take on this:

=SUM(B1:D1)-IF(SMALL(B1:D1,1),SMALL(B1:D1,3),0)

= Sum(3 scores) - IF(no 0, largest, otherwise 0)

**Ron Coderre** · 08-03-2007, 11:27 PM

With values in B2:D2

Try this formula
=SUM(IF(COUNTIF(B2:D2,">0")=3,SMALL(B2:D2,{1,2}),(B2:D2)))

If 3 values > 0: sum of lower 2
If 0 or 1 or 2 values > 0: sum of all

Is that something you can use?

**mikerickson** · 08-03-2007, 11:29 PM

=sum(b1:d1)-max(b1:d1)*sign(min(b1:d1))

**timmy5712** · 08-07-2007, 10:14 PM

thanks to all. each suggestion worked!!! brilliant.
The only issue remaining is this -
if a player misses any two rounds (i.e. has two zeroes recorded) the only round played would incorrectly place that player ahead of the best combined two rounds. e.g. someone plays only the second round & scores 93. that wd position ahead of combined 2 round total of 138 (70+68).
can i add to the formula by returning answer = blank for any range that includes two zero scores?
note: i finally list all records by Data/sort/ascending on the values in "Total, Best 2 rounds" column

**Paul** · 08-08-2007, 01:43 AM

Might I suggest adding two columns? Rather than sorting by your '2 best rounds' column, try using this array formula in another column (for example, column F, starting in F2 if your player data starts in A2), and fill downward for 2nd best, 3rd best, etc..:

=SMALL(IF($E$2:$E$20>120,$E$2:$E$20),ROW(A1))

After typing this formula you must press CTRL+SHIFT+ENTER, otherwise it won't work properly.

I made two assumptions with this formula:
1. Nobody will shoot higher than 120 in any one round
2. Nobody will shoot lower than 120 in any two combined rounds

If this isn't true, you may need to adjust 120 higher or lower.

Then in column G, cell G2, use the formula:

=INDEX(A2:A20,MATCH(F2,E2:E20,0))

Just press ENTER after typing this formula, it's not an array formula. Fill that formula down as many rows as necessary and it will return the names associated with the ranked scores in column F.