I need to find the sum of the best of 6 scores.

  • Thread starter Thread starter okracerx
  • Start date Start date
O

okracerx

I need to be able to calculate the sum of, "the best of 6". Here is an
example:

Andy Harris 60 54 48 38 60 60 60 22 34

Total is 342

Would like a formula that would figure this out for me.

Thanks
 
=LARGE(B1:K1,1)+LARGE(B1:K1,2)+LARGE(B1:K1,3)+LARGE(B1:K1,4)+LARGE(B1:K1,5)+LARGE(B1:K1,6)
 
Use this formula...

=SUM(LARGE(B1:J1,{1,2,3,4,5,6}))

Change the cell reference B1:J1 to your desired range, if required.

If this post helps, Click Yes!
 
Thanks Don,
Works great, but what if one of my cells has text "DNF", value is zero. but
when i use the above forumla it give me a #num error. Thanks
 
"DNF". Are you involved with racing? I was once an SCCA Formula Ford driver
and car importer.

That's because you have less than 6 number entries. You could increase the
range and put in some 0's
So use this instead. Be advised that you must enter using CSE
(control+shift+enter). If you have only 2 numbers in the range it will count
only those 2. If you have 6 it will count 6.
=SUM(LARGE(A27:Z27,ROW(INDIRECT("1:"&COUNT(A27:Z27)))))
 
But if there are 26 entries, it'll sum all of them.

Maybe:
=SUM(LARGE(A27:Z27,ROW(INDIRECT("1:"&MIN(6,COUNT(A27:Z27))))))
(still array entered)
(sums no more than 6 entries)

And if there may not be any numbers:
=IF(COUNT(A27:Z27)=0,"No numbers",
SUM(LARGE(A27:Z27,ROW(INDIRECT("1:"&MIN(6,COUNT(A27:Z27)))))))
(still array entered)
 
Or restrict the range to 6 columns. OR>>>
=SUM(LARGE(A27:Z27,ROW(INDIRECT("1:"&IF(COUNT(A27:Z27)>6,6,COUNT(A27:Z27))))))
 
After looking at the file and getting more detail, I send OP this

Sub PlaceFormulasSAS() 'Calculates column N
Application.ScreenUpdating = False
Application.Calculation = xlManual
For i = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
cn = 7
If IsNumeric(Cells(i, 2)) And Cells(i, 2) > 0 Then
If Cells(i, 1).End(xlUp).Interior.ColorIndex = 10 Then cn = cn - 1
Cells(i, "N").FormulaArray = _
"=IF(COUNTa(C" & i & ":K" & i & ")<5,"""",SUM(LARGE(C" & i & ":K" & i &
",ROW(INDIRECT(""1:""&IF(COUNT(C" & i & ":K" & i & ")>" & cn & "," & cn &
",COUNT(C" & i & ":K" & i & ")))))))"
End If
Next i

'comment line below to leave the formula
Columns("n").Value = Columns("n").Value

Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub
 
Back
Top