|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
November 12th, 2009, 11:55 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
I need to find the sum of the best of 6 scores.
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 
|
|
#2
November 13th, 2009, 12:05 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
I need to find the sum of the best of 6 scores.
=SUM(LARGE(A27:J27,{1,2,3,4,5,6}))
-- Don Guillett Microsoft MVP Excel SalesAid Software "okracerx" wrote in message ... 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
|
|
#3
November 13th, 2009, 12:47 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
I need to find the sum of the best of 6 scores.
=LARGE(B1:K1,1)+LARGE(B1:K1,2)+LARGE(B1:K1,3)+LARG E(B1:K1,4)+LARGE(B1:K1,5)+LARGE(B1:K1,6)
-- Gary''s Student - gsnu200908 "okracerx" wrote: 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
|
|
#4
November 13th, 2009, 12:56 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
I need to find the sum of the best of 6 scores.
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! -------------------- (Ms-Exl-Learner) -------------------- "okracerx" wrote: 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
|
|
#5
November 13th, 2009, 07:21 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
I need to find the sum of the best of 6 scores.
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 "Don Guillett" wrote: =SUM(LARGE(A27:J27,{1,2,3,4,5,6})) -- Don Guillett Microsoft MVP Excel SalesAid Software "okracerx" wrote in message ... 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 .
|
|
#7
November 13th, 2009, 08:52 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
I need to find the sum of the best of 6 scores.
But if there are 26 entries, it'll sum all of them.
Maybe: =SUM(LARGE(A27:Z27,ROW(INDIRECT("1:"&MIN(6,COUNT(A 27: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(A2 7:Z27))))))) (still array entered) Don Guillett wrote: "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 ))))) -- Don Guillett Microsoft MVP Excel SalesAid Software "okracerx" wrote in message ... 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 "Don Guillett" wrote: =SUM(LARGE(A27:J27,{1,2,3,4,5,6})) -- Don Guillett Microsoft MVP Excel SalesAid Software "okracerx" wrote in message ... 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 . -- Dave Peterson
|
|
#8
November 13th, 2009, 09:11 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
I need to find the sum of the best of 6 scores.
Or restrict the range to 6 columns. OR
=SUM(LARGE(A27:Z27,ROW(INDIRECT("1:"&IF(COUNT(A27: Z27)6,6,COUNT(A27:Z27)))))) -- Don Guillett Microsoft MVP Excel SalesAid Software "Dave Peterson" wrote in message ... But if there are 26 entries, it'll sum all of them. Maybe: =SUM(LARGE(A27:Z27,ROW(INDIRECT("1:"&MIN(6,COUNT(A 27: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(A2 7:Z27))))))) (still array entered) Don Guillett wrote: "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 ))))) -- Don Guillett Microsoft MVP Excel SalesAid Software "okracerx" wrote in message ... 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 "Don Guillett" wrote: =SUM(LARGE(A27:J27,{1,2,3,4,5,6})) -- Don Guillett Microsoft MVP Excel SalesAid Software "okracerx" wrote in message ... 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 . -- Dave Peterson
|
|
#9
November 15th, 2009, 03:25 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
I need to find the sum of the best of 6 scores.
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 -- Don Guillett Microsoft MVP Excel SalesAid Software "okracerx" wrote in message ... 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
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
