Formula to pull text associated with Top 5 variances either positive or negative

[ShanaVT]

I have a large list of Programs and I need to easily be able to pull the Programs that have the Top 5 variances both positive and negative. Below is the sample data. Any help much appreciated!

[TABLE="width: 310"]
<colgroup><col><col span="2"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]Actual[/TD]
[TD]Plan[/TD]
[TD]Variance[/TD]
[/TR]
[TR]
[TD]Program 1[/TD]
[TD] 5,000[/TD]
[TD] 3,000[/TD]
[TD] 2,000[/TD]
[/TR]
[TR]
[TD]Program 2[/TD]
[TD] 200[/TD]
[TD] 600[/TD]
[TD] (400)[/TD]
[/TR]
[TR]
[TD]Program 3[/TD]
[TD] 700[/TD]
[TD] 800[/TD]
[TD] (100)[/TD]
[/TR]
[TR]
[TD]Program 4[/TD]
[TD] 800[/TD]
[TD] 7,000[/TD]
[TD] (6,200)[/TD]
[/TR]
[TR]
[TD]Program 5[/TD]
[TD] 2,000[/TD]
[TD] 1,000[/TD]
[TD] 1,000[/TD]
[/TR]
[TR]
[TD]Program 6[/TD]
[TD] 9,000[/TD]
[TD] 200[/TD]
[TD] 8,800[/TD]
[/TR]
[TR]
[TD]Program 7[/TD]
[TD] 1,000[/TD]
[TD] 300[/TD]
[TD] 700[/TD]
[/TR]
[TR]
[TD]Program 8[/TD]
[TD] 500[/TD]
[TD] 6,000[/TD]
[TD] (5,500)[/TD]
[/TR]
[TR]
[TD]Program 9[/TD]
[TD] 300[/TD]
[TD] 200[/TD]
[TD] 100[/TD]
[/TR]
[TR]
[TD]Program 10[/TD]
[TD] 100[/TD]
[TD] 600[/TD]
[TD] (500)[/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD] 19,600[/TD]
[TD] 19,700[/TD]
[TD] (100)[/TD]
[/TR]
</tbody>[/TABLE]


And the desired results would be:

[TABLE="width: 80"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Top 5[/TD]
[/TR]
[TR]
[TD]Program 6[/TD]
[/TR]
[TR]
[TD]Program 4[/TD]
[/TR]
[TR]
[TD]Program 8[/TD]
[/TR]
[TR]
[TD]Program 1[/TD]
[/TR]
[TR]
[TD]Program 5[/TD]
[/TR]
</tbody>[/TABLE]

 

[DRSteele]

This ought to do it. The key here is that we must deal with potential ties. Your top five Variances in a list of 10,000 Programs could report a huge number of Programs for the top five. In my case I added one duplicate of 1000, resulting in a top five list of six Programs. So you have to copy the REPORT grid down enough rows to cover all the potential ties. I also added a helper column for the magnitude of the variance, but you could use function ABS inside these formulas if you want to omit it.

Copy these formulas in E3 and in A22:C22 downwards as required.

Review this video from ExcelIsFun, which is the exact procedure I used. https://www.youtube.com/watch?v=rKDI-kdBsjY

	A	B	C	D	E
Program 1
Program 2
Program 3
Program 4
Program 5
Program 6
Program 7
Program 8
Program 9
Program 10
p11
Top
Hurdle
Records

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #DDEBF7"]DATA[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FFF2CC"]Program[/TD]
[TD="bgcolor: #FFF2CC"]Actual[/TD]
[TD="bgcolor: #FFF2CC"]Plan[/TD]
[TD="bgcolor: #FFF2CC"]Variance[/TD]
[TD="bgcolor: #FFF2CC"]Magnitude[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]5,000[/TD]
[TD="align: right"]3,000[/TD]
[TD="align: right"]2,000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]2000[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]200[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]-400[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]400[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]700[/TD]
[TD="align: right"]800[/TD]
[TD="align: right"]-100[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]100[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]800[/TD]
[TD="align: right"]7,000[/TD]
[TD="align: right"]-6,200[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]6200[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]2,000[/TD]
[TD="align: right"]1,000[/TD]
[TD="align: right"]1,000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]1000[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]9,000[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]8,800[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]8800[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]1,000[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]700[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]700[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]500[/TD]
[TD="align: right"]6,000[/TD]
[TD="align: right"]-5,500[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]5500[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]300[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]100[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]100[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]100[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]-500[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]500[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]100000[/TD]
[TD="align: right"]99000[/TD]
[TD="align: right"]1,000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]1000[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="bgcolor: #DDEBF7"]REPORT[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]1000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="bgcolor: #FCE4D6"]No[/TD]
[TD="bgcolor: #FCE4D6"]Magnitude[/TD]
[TD="bgcolor: #FCE4D6"]Program[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]8800[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "]Program 6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]6200[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "]Program 4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]5500[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "]Program 8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]2000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "]Program 1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]26[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]1000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "]Program 5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]27[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]1000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "]p11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]28[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet29​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E3[/TH]
[TD="align: left"]=ABS(D3)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B18[/TH]
[TD="align: left"]=LARGE(E3:E13,B17)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B19[/TH]
[TD="align: left"]=COUNTIFS(E3:E13,">="&B18)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A22[/TH]
[TD="align: left"]=IF(ROWS(A$22:A22)>$B$19,"",ROWS(A$22:A22))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B22[/TH]
[TD="align: left"]=IF(A22="","",LARGE($E$3:$E$13,A22))[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C22[/TH]
[TD="align: left"]=IF(A22="","",INDEX($A$3:$A$13,AGGREGATE(15,6,(ROW($E$3:$E$13)-ROW($E$3)+1)/($E$3:$E$13=B22),COUNTIF($B$22:B22,B22))))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]