Addition by rank order

[jeebs83]

I'm trying to figure out how to sum numbers in rank order. Example: I have a set of numbers (8, 9, 10, 11, 12), and would like to add the Top 2 and Top 3. How would I set up a formula to add 12+11 and 12+11+10?

 

[MrKowz]

try:

=SUM(LARGE(A:A,{1,2}))+SUM(LARGE(A:A,{1,2,3}))

 

[jeebs83]

That formula does the trick, but the larger problem is that I am trying to do this for several fields. If the rank cell = 1, I only want the top ranked number. If the rank cell = 2, I want to add 1st and 2nd largest, if rank cell = 3, I want to add 1st/2nd/3rd largest, etc. How would I go about it?

 

[MrKowz]

This is going to require a UDF, I believe.

To use this, press Alt+F11 to open the VBA editor. Then go to Insert>Module, and copy/paste the following code into that window:

Public Function SumRank(rng As Range, num As Long)
Dim i As Long
For i = 1 To num
    SumRank = SumRank + Application.Large(rng, i)
Next i
End Function

Then follow this example:

Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right"></TD><TD>Top Numbers</TD><TD style="TEXT-ALIGN: right">3</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right"></TD><TD>Sum</TD><TD style="TEXT-ALIGN: right">36</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: right">6</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="TEXT-ALIGN: right">7</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="TEXT-ALIGN: right">8</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="TEXT-ALIGN: right">9</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="TEXT-ALIGN: right">10</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD style="TEXT-ALIGN: right">11</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD style="TEXT-ALIGN: right">12</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">13</TD><TD style="TEXT-ALIGN: right">13</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR></TBODY></TABLE><TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH>Sheet1</TH></TR></TD></THEAD><TBODY></TBODY></TABLE>

<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>D2</TH><TD style="TEXT-ALIGN: left">=sumrank(A1:A13,D1)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

 

[jeebs83]

The "sumrank" works, but when I encounter a tie in rank, I run into a pickle. For example, lets say the 1st ranked numeral is 30%, 2nd is 25%, 3rd ranked numeral is 20%, and there is a tie for 4th (say 10%). Under the formulas, the array would look like this:

30%, 55%, 75%, 85%, 85%

Since there is a tie for the 4th ranked sumrank, the formula naturally recognizes the tie for 4th as equal. What I'd like to do is add up any ties, so that when I encounter such a tie, I would get this result:

30%, 55%, 75%, 85%, 95%

Any advice based on the above thread?