+ Reply to Thread
Results 1 to 7 of 7

Excel formula to find top 10 values by year

  1. #1
    Registered User
    Join Date
    02-14-2018
    Location
    Delhi
    MS-Off Ver
    2013
    Posts
    5

    Excel formula to find top 10 values by year

    So, I have long sheet with multiple values by year. Need help extracting the top ten values by year using a formula. E.g. Year 2013 - Top ten values

    02/12/2018 2018 Michal Olivo 148.71
    02/09/2018 2018 Basilia Worthy 2.03
    02/09/2018 2018 Madeleine Seabaugh 0.072
    02/09/2018 2018 Phoebe Grennan -
    02/09/2018 2018 Rosita Denman 6.38
    02/09/2018 2018 Pok Seay -
    02/09/2018 2018 Hershel Creamer -
    02/09/2018 2018 Zulema Dewall -
    02/09/2018 2018 Xiomara Fowkes 1.07
    02/09/2018 2018 Neville Stillings 1.0
    02/09/2018 2018 Eugena Tinney 47.63
    02/08/2018 2018 Daniele Sottile -
    02/08/2018 2018 Camila Deluca 158.16
    02/07/2018 2018 Shanta Leday -
    02/07/2018 2018 China Pearlman 59.92
    02/07/2018 2018 Moriah Bigby 65.0
    12/30/2017 2017 Shaunda Onorato 0.267
    12/30/2017 2017 Jenelle Erskine -
    12/29/2017 2017 Ines Killgore -
    12/29/2017 2017 Carter Thibeault 36.82
    12/29/2017 2017 Adena Huot 1.08
    12/29/2017 2017 Ivonne Sturman -
    12/29/2017 2017 Irina Maxey 13.83
    12/29/2017 2017 Adelaide Denmark 0.942
    12/28/2017 2017 Mariko Hausner 0.012
    12/28/2017 2017 Nicol Toler -
    12/28/2017 2017 Felecia Yandell 0.657
    12/28/2017 2017 Lucas Faulks -
    12/28/2017 2017 Lurline Dubberly 290.85
    12/28/2017 2017 Werner Schuman -
    12/28/2017 2017 Lai Masson 73.48
    12/28/2017 2017 Celena Cid 0.935
    12/28/2017 2017 Nestor Delahoussaye 4.62
    12/28/2017 2017 Chanel Judon -
    12/28/2017 2017 Arla Towle 6.08
    12/27/2017 2017 Alesia Doggett 61.12
    12/27/2017 2017 Luanne Hain 0.935
    12/26/2017 2017 Cecil Mericle 78.06
    12/26/2017 2017 Scarlett Nieman -
    12/26/2017 2017 Virgilio Homer -
    12/31/2016 2016 Elidia Shillings 24.68
    12/31/2016 2016 Aldo Hunsinger 15.0
    12/31/2016 2016 Lessie Boughton 5.0
    12/31/2016 2016 Larisa Holmon -
    12/31/2016 2016 Chester Vandoren 4.84
    12/31/2016 2016 Starla Weymouth 0.096
    12/31/2016 2016 Sherron Bower -
    12/30/2016 2016 Bethann Privett -
    12/30/2016 2016 Roselia Trask -
    12/30/2016 2016 Shakia Rudnick -
    12/30/2016 2016 Jeanne Sabb 185.19
    12/30/2016 2016 Inger Olivieri 50.41
    12/30/2016 2016 Tiffany Polite 0.25
    12/30/2016 2016 Tod Waterbury 18.43
    12/30/2016 2016 Miranda Ariola -
    12/30/2016 2016 Annalisa Coffman 1.82
    12/29/2016 2016 Rory Langston -
    12/29/2016 2016 Shenna Biel 57.52
    12/29/2016 2016 Donna Viviani 2.96
    12/31/2015 2015 Kayla Kumm -
    12/31/2015 2015 Apryl Aschenbrenner -
    12/31/2015 2015 Joslyn Juckett -
    12/31/2015 2015 Merissa Musgrave 16.73
    12/31/2015 2015 Carla Casner 12.0
    12/31/2015 2015 Ashely Arciniega -
    12/31/2015 2015 Larue Laraway 0.491
    12/31/2015 2015 Hyo Hellman 89.63
    12/31/2015 2015 Lorrine Lampton -
    12/31/2015 2015 Chasidy Consolini -
    12/31/2015 2015 Dorathy Donlin -
    12/30/2015 2015 Fredia Ferrara 125.0
    12/30/2015 2015 Hester Hakes 0.295
    12/30/2015 2015 Ciera Comerford 31.22
    12/30/2015 2015 Emiko Ewan 2.75
    12/30/2015 2015 Vinnie Victory 154.08
    12/30/2015 2015 Georgina Greene -
    12/30/2015 2015 Bambi Baines 30.82
    12/29/2015 2015 Sanjuanita Servantes 0.303
    12/29/2015 2015 Chere Collazo 0.3

  2. #2
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Excel formula to find top 10 values by year

    A pivot table should do this if you post a sample.

  3. #3
    Registered User
    Join Date
    02-14-2018
    Location
    Delhi
    MS-Off Ver
    2013
    Posts
    5

    Re: Excel formula to find top 10 values by year

    Not with a pivot - need to use a formula. Sample enclosed above for four columns

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Excel formula to find top 10 values by year

    Hi vikram1690. Welcome to the forum.

    With data in columns A:D this helper formula in column E.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    With a drop down (or type in the year) in $G$1 this formula in F1 and filled down.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Dave

  5. #5
    Valued Forum Contributor
    Join Date
    11-28-2015
    Location
    indo
    MS-Off Ver
    2016 64 bitt
    Posts
    1,269

    Re: Excel formula to find top 10 values by year

    =if(rows($f$1:f1)>10;"";vlookup(large($a$1:$a$79;rows($j$1:j1));$a$1:$d$79;4;0))

  6. #6
    Registered User
    Join Date
    02-14-2018
    Location
    Delhi
    MS-Off Ver
    2013
    Posts
    5

    Re: Excel formula to find top 10 values by year

    Thank you good sir! Appreciate the help

  7. #7
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: Excel formula to find top 10 values by year

    As per post sample on post #4

    Give this try without helper column

    F1=IF(ROWS(A$1:A1)>10,"",LARGE(INDEX(($B$1:$B$79=$G$1)*$D$1:$D$79,),ROWS($F$1:F1)))

    copy down!
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Formula to find Quarter of year
    By preveo in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-06-2017, 10:06 AM
  2. [SOLVED] countifs to find the total count between this year and last year for series of data
    By maher2014 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-06-2016, 02:39 AM
  3. Replies: 1
    Last Post: 03-24-2015, 01:42 PM
  4. [SOLVED] Find last 12 values based on week number/year
    By pytheus in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-10-2014, 05:14 AM
  5. Need help with formula for find time (in months) for any given financial year
    By chandni_sharma in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 07-14-2013, 11:52 AM
  6. Replies: 4
    Last Post: 07-16-2012, 07:02 AM
  7. Formula to find day number of year
    By Radman in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-29-2008, 03:00 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1