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

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!

ActualPlanVariance
Program 1 5,000 3,000 2,000
Program 2 200 600 (400)
Program 3 700 800 (100)
Program 4 800 7,000 (6,200)
Program 5 2,000 1,000 1,000
Program 6 9,000 200 8,800
Program 7 1,000 300 700
Program 8 500 6,000 (5,500)
Program 9 300 200 100
Program 10 100 600 (500)
Total 19,600 19,700 (100)

<colgroup><col><col span="2"><col></colgroup><tbody>
</tbody>


And the desired results would be:

Top 5
Program 6
Program 4
Program 8
Program 1
Program 5

<colgroup><col></colgroup><tbody>
</tbody>
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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
1DATA
2ProgramActualPlanVarianceMagnitude
3Program 15,0003,0002,0002000
4Program 2200600-400400
5Program 3700800-100100
6Program 48007,000-6,2006200
7Program 52,0001,0001,0001000
8Program 69,0002008,8008800
9Program 71,000300700700
10Program 85006,000-5,5005500
11Program 9300200100100
12Program 10100600-500500
13p11100000990001,0001000
14
15
16REPORT
17Top5
18Hurdle1000
19Records6
20
21NoMagnitudeProgram
2218800Program 6
2326200Program 4
2435500Program 8
2542000Program 1
2651000Program 5
2761000p11
28

<tbody>
</tbody>
Sheet29

Worksheet Formulas
CellFormula
E3=ABS(D3)
B18=LARGE(E3:E13,B17)
B19=COUNTIFS(E3:E13,">="&B18)
A22=IF(ROWS(A$22:A22)>$B$19,"",ROWS(A$22:A22))
B22=IF(A22="","",LARGE($E$3:$E$13,A22))
C22=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))))

<tbody>
</tbody>

<tbody>
</tbody>
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

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