+ Reply to Thread
Results 1 to 19 of 19

Function that rank by number of ocurence the 5th values

  1. #1
    Registered User
    Join Date
    02-16-2016
    Location
    London
    MS-Off Ver
    2010
    Posts
    9

    Function that rank by number of ocurence the 5th values

    Hello,

    I'd like to know if there are a function that rank 5th by their number of occurence.

    Here is an exemple :
    Sans titre.png

    Indeed, the n°1 is "a" because it appears 5 times in the selection, n°2 "c" because 4 times and "b" n°3 beacause 3.
    So there would be only one selection area A1:A12.

    Do you think it is possible ?

  2. #2
    Valued Forum Contributor
    Join Date
    12-28-2014
    Location
    NSW, Australia
    MS-Off Ver
    MS365
    Posts
    604

    Re: Function that rank by number of ocurence the 5th values

    This example uses 2 helper columns to count the occurrences and then a vlookup + large to obtain the rankings.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    02-16-2016
    Location
    London
    MS-Off Ver
    2010
    Posts
    9

    Re: Function that rank by number of ocurence the 5th values

    Hello,

    Thanks. But isn't there is a way of auto-determine the value without having to manually count the number of ocurence for each of them.
    Because I have a hundred different values, and I only want the top 5 of them.
    Have you got any idea ?

    Thanks

  4. #4
    Valued Forum Contributor
    Join Date
    12-28-2014
    Location
    NSW, Australia
    MS-Off Ver
    MS365
    Posts
    604

    Re: Function that rank by number of ocurence the 5th values

    Not me sorry, except to extend columns E & F to allow for all possible results.
    Need a proper expert to come in and save the day :D

  5. #5
    Valued Forum Contributor Gatti's Avatar
    Join Date
    06-08-2015
    Location
    Brasil, São Paulo, Ribeirão Preto
    MS-Off Ver
    365
    Posts
    346

    Re: Function that rank by number of ocurence the 5th values

    Hello...

    There is a way to solve it:

    On first Column (A) I put values between A, B, C, D, E, F and G.

    The result will be given on Column C and D

    Please Login or Register  to view this content.
    Last edited by Gatti; 02-16-2016 at 11:57 AM.

  6. #6
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Function that rank by number of ocurence the 5th values

    assuming your range is in A1 to A12 then in C1
    copy paste below then HOLD CONTROL AND SHIFT TOGETHER AND THEN HIT ENTER TO MAKE IT ARRAY FORMULA
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Drag down
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST

    More we learn about excel, more it shows us, how less we know about it.

    for chemistry
    https://www.youtube.com/c/chemistrybyshivaansh

  7. #7
    Registered User
    Join Date
    02-16-2016
    Location
    London
    MS-Off Ver
    2010
    Posts
    9

    Re: Function that rank by number of ocurence the 5th values

    Quote Originally Posted by Gatti View Post
    Hello...

    There is a way to solve it:

    On first Column (A) I put values between A, B, C, D, E, F and G.

    The result will be given on Column C and D

    Please Login or Register  to view this content.
    Sorry but I don't understand what I am supposed to do with this code ? Can I put it in a cell ?

  8. #8
    Registered User
    Join Date
    02-16-2016
    Location
    London
    MS-Off Ver
    2010
    Posts
    9

    Re: Function that rank by number of ocurence the 5th values

    Quote Originally Posted by hemesh View Post
    assuming your range is in A1 to A12 then in C1
    copy paste below then HOLD CONTROL AND SHIFT TOGETHER AND THEN HIT ENTER TO MAKE IT ARRAY FORMULA
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Drag down
    The formula you gives me seems to have an error..
    Sans titre.png

    Or is it me who make it wrong ?

  9. #9
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Function that rank by number of ocurence the 5th values

    array formulas are ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

    Try below in C1 in that case
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Drag down upto five cells

  10. #10
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Function that rank by number of ocurence the 5th values

    Other way is to use pivot table give a header In A1 as DATA
    Then click insert click pivot table in Row area keep Data and in value also Keep data that will give you count of each

  11. #11
    Registered User
    Join Date
    02-16-2016
    Location
    London
    MS-Off Ver
    2010
    Posts
    9

    Re: Function that rank by number of ocurence the 5th values

    Yes I have ever done array formula..
    But these ones really don't want to work, have you tried ?
    Can you give me an excel file with the formula in ?

    Thanks

  12. #12
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Function that rank by number of ocurence the 5th values

    find attached first sheet with formula and second one with pivot table
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    02-16-2016
    Location
    London
    MS-Off Ver
    2010
    Posts
    9

    Re: Function that rank by number of ocurence the 5th values

    Thanks you very much ! It seems to work
    Just I extend till A86, and in the top 5 apperas two redondant results, you know why ?

    \1

    Here is the command :

    =SI(LIGNES($A$1:A1)>5;"";SIERREUR(INDEX($A$2:$A$86;EQUIV(VRAI;FREQUENCE(EQUIV($A$2:$A$86;$A$2:$A$86;0);EQUIV($A$2:$A$86;$A$2:$A$86;0))=GRANDE.VALEUR(SI(FREQUENCE(EQUIV($A$2:$A$86;$A$2:$A$86;0);EQUIV($A$2:$A$86;$A$2:$A$86;0))>1;FREQUENCE(EQUIV($A$2:$A$86;$A$2:$A$86;0);EQUIV($A$2:$A$86;$A$2:$A$86;0)));LIGNES($A$1:A1));0));INDEX($A$2:$A$86;PETITE.VALEUR(SI(NB.SI($A$2:$A$86;$A$2:$A$86)=1;LIGNE($A$2:$A$86));LIGNES($A$1:A1)-SOMME(--(FREQUENCE(EQUIV($A$2:$A$86;$A$2:$A$86;0);EQUIV($A$2:$A$86;$A$2:$A$86;0))>1))))))

    PS : sorry I don't know how to upload a file on the forum

  14. #14
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Function that rank by number of ocurence the 5th values

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic

  15. #15
    Registered User
    Join Date
    02-16-2016
    Location
    London
    MS-Off Ver
    2010
    Posts
    9

    Re: Function that rank by number of ocurence the 5th values

    Hello,

    Sorry for the delay, very busy.
    I still don't know how to upload :

    1) Fist method :

    Sans titre.png

    Just a blank field appears, I cannot upload any files..

    2) Second method, with the "Manage attachments"

    Sans titre1.png

    Once I've upload a file, I don't know what to do for made him appears in my reply...

    Can you help ?

  16. #16
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Function that rank by number of ocurence the 5th values

    click close window at bottom then click save changes

  17. #17
    Registered User
    Join Date
    02-16-2016
    Location
    London
    MS-Off Ver
    2010
    Posts
    9

    Re: Function that rank by number of ocurence the 5th values

    Ok I upload
    Last edited by tot94; 02-24-2016 at 11:57 AM.

  18. #18
    Registered User
    Join Date
    02-16-2016
    Location
    London
    MS-Off Ver
    2010
    Posts
    9

    Re: Function that rank by number of ocurence the 5th values

    You'll see there are 5 different values.
    But the n°2 and n°3 is the same and the n°5 is not fill...

    Don't understand why

    Here is the excel :
    Attached Files Attached Files

  19. #19
    Valued Forum Contributor
    Join Date
    07-29-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2003/Excel 2010
    Posts
    534

    Re: Function that rank by number of ocurence the 5th values

    Different approach, based on the function "MODE"
    Attached Files Attached Files

+ 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. [SOLVED] Using Index and Rank to show a specific number of values from multiple lists
    By Rex411 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 05-06-2015, 08:02 PM
  2. Replies: 8
    Last Post: 02-20-2014, 07:03 PM
  3. NoobQ: Rank function treats negative values as positive values. Help!
    By lutonoodles in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-12-2013, 02:10 PM
  4. [SOLVED] Rank function ignoring zero and negative values
    By mfortier3 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-12-2013, 05:37 PM
  5. [SOLVED] Get text/string from a cell based on it's Nth Ocurence
    By WITJ in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-13-2013, 06:04 PM
  6. [SOLVED] The RANK() function cannot ignore error values
    By BNCOXUK in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-27-2012, 08:44 AM
  7. [SOLVED] Rank Function and Number that are close together
    By Peter Gundrum Milwaukee WI in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-02-2006, 03:55 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