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

S

ShanaVT

Board Regular
Joined
May 12, 2010
Messages
86
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]
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
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

ABCDE
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]
 
Upvote 0
You must log in or register to reply here.

Similar threads

MrDB4Excel
  • Question Question
If Find Blank Cell
Replies
0
Views
641
MrDB4Excel
MrDB4Excel
A
  • Solved
Possible If and something else?!
Replies
8
Views
484
Alex Blakenburg
A
RAJESH1960
  • Question Question
Formula to display data of multiple columns in a horizontal order
Replies
3
Views
469
RAJESH1960
RAJESH1960
D
  • Question Question
Difficult Excel Formula - Populating Data from Drop Down Menu
Replies
0
Views
600
DataValidator
D
J
  • Question Question
Categorizing formula
Replies
2
Views
447
jwillits
J

Forum statistics

Threads
1,223,243
Messages
6,170,967
Members
452,371
Latest member
Frana

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top