hi all, is there any quick method to add up 3 biggest numbers in a set
of number? e.g.
I have the following data in a row:
100 20 90 30
And I want to add up 3 biggest numbers, i.e.
100 + 90 + 30 = 220
Thanks!
I wrote a custom function to do just this a couple of years back. For
top 1,2 or 3 then you could always use the
Large(range,1)+Large(range,2) etc which is fine for a couple of
values, but get tedious for the sum of the top 20.
Might not be the most efficient code... but it worked for me
Function SumTopX(Selection, TopNo As Integer)
'Function to return the top X values in a range.
'Inputs - Range to look through eg a1:a20
' No of top items to return eg 3 or 10
'Outputs - Returns sum of top x values
Dim TopVals() As Double
Dim n As Integer
Dim m As Integer
If TypeName(Selection) <> "Range" Then Exit Function
'Allocate TopNo elements
ReDim TopVals(TopNo)
For Each cell In Selection
For n = 1 To TopNo
If cell.Value >= TopVals(n) Then
'Need to move everything down the array
For m = TopNo To (n + 1) Step -1
TopVals(m) = TopVals(m - 1)
Next m
'Move the cell value to topvals
TopVals(n) = cell.Value
Exit For
End If
Next n
Next cell
For n = 1 To TopNo
SumTopX = SumTopX + TopVals(n)
Next n
End Function
Good luck
Noz
