Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

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

  1. #1
    Board Regular
    Join Date
    May 2010
    Posts
    75
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    Board Regular DRSteele's Avatar
    Join Date
    Mar 2015
    Location
    Calgary
    Posts
    1,522
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default 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))))
    Windows10, Excel 2016 (365 Insider)
    My formulas are always in
    Green
    You can get the HTML Maker.

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com