Thanks:  0
Likes:  0

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

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

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!

 Actual Plan Variance 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)

And the desired results would be:

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

2. ## Re: Formula to pull text associated with Top 5 variances either positive or negative

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
1 DATA
2 Program Actual Plan Variance Magnitude
3 Program 1 5,000 3,000 2,000 2000
4 Program 2 200 600 -400 400
5 Program 3 700 800 -100 100
6 Program 4 800 7,000 -6,200 6200
7 Program 5 2,000 1,000 1,000 1000
8 Program 6 9,000 200 8,800 8800
9 Program 7 1,000 300 700 700
10 Program 8 500 6,000 -5,500 5500
11 Program 9 300 200 100 100
12 Program 10 100 600 -500 500
13 p11 100000 99000 1,000 1000
14
15
16 REPORT
17 Top 5
18 Hurdle 1000
19 Records 6
20
21 No Magnitude Program
22 1 8800 Program 6
23 2 6200 Program 4
24 3 5500 Program 8
25 4 2000 Program 1
26 5 1000 Program 5
27 6 1000 p11
28
Sheet29

Worksheet Formulas
Cell Formula
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))))

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•