1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

What are 5 numbers in the highest frequency,

Discussion in 'Excel Challenges' started by bines53, Sep 11, 2016.

  1. bines53

    bines53 Active Member

    Messages:
    641
    Hello friends,

    Challenge is not difficult at all, the focus is to find the shortest formula.

    Conditioning is, without volatile functions.

    Order values should be is ,18,19,16,17,20.

    My solution, next Monday.

    David

    Attached Files:

  2. John Jairo V

    John Jairo V Well-Known Member

    Messages:
    377
    Hi @bines53!

    At first sight, you could use an array formulae (in H2):
    =MODE(IF(COUNTIF(H$1:H1,A$1:D$36),"",A$1:D$36))

    Blessings!
    Thomas Kuriakose and r2c2 like this.
  3. bines53

    bines53 Active Member

    Messages:
    641
  4. XOR LX

    XOR LX Active Member

    Messages:
    176
    Hi,

    My idealist streak can't help but notice the 'convenience' that the intended solutions are to begin in row 2.

    What happens if we change it so that we insist on the first result being in row 1?

    Forgive me for appearing pedantic; obviously in practice we would quite happily choose whichever row we wish to begin our required list of solutions. It's just that, to me, there seems something slightly 'less than perfect' about a solution which is row-dependent.

    It's for similar reasons that I've always felt a slight pang of distaste whenever I see the construction with MATCH(0,COUNTIF... for listing distinct entries from a range; inside I'm always wanting to ask the poster "But I want to list my solutions beginning in row 1!"

    Anyway, sorry for spoiling the fun! After all, it's your challenge, not mine! :)
    John Jairo V and NARAYANK991 like this.
  5. bines53

    bines53 Active Member

    Messages:
    641
  6. John Jairo V

    John Jairo V Well-Known Member

    Messages:
    377
    Hi @XOR LX !

    It's clear your point! And I Agree with you. The solution provided above taking the previous value for calculate the next one... maybe it's not the best thing, but the challenge says the shortest one. You have a entire post explain how we can do it, and amazing reply by Lori:

    https://excelxor.com/2014/12/09/uni...-frequent-numbers-in-a-two-dimensional-range/

    Blessings!
    P.D: I've missed your wonderful post on your page. I hope to see more soon (if possible). Blessings!
    P.D2: Sorry for my English, is not my native language.
  7. bines53

    bines53 Active Member

    Messages:
    641
    Hello friends,

    I want to note a significant advantage of the proposed method, which apparently
    have not noticed, the last three of the five numbers, they have the same number of returned ,13 times.

    To solve this problem, it requires a very long formula.

    David
    Last edited: Sep 13, 2016
  8. bines53

    bines53 Active Member

    Messages:
    641
    Hello friends,

    This is my fourth challenging question, and have a desire to continue, but I can not see friends here do not really like the questions or the answers.
    For example, this challenge John Jairo V,One of the brightest people I know,
    Gave the only possible answer, and I can not find a reason not to receive LIKE !
    Need to know to compliment !

    Regards
    David
  9. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,780
    David,

    I will agree that no one has given a like (that includes you as you have replied but not pressed the button ;) ) but that doesn't mean that no one likes the solution or the discussion that happens in a challenge.

    What I did not understand why it is so important to "like" it using the like button? What if this forum did not have this feature? Will you still perceive that no one is appreciating it? MrExcel has this feature as well but you can't trace the like to the post and giver. Some other forums don't have it. Do not come to a conclusion on the basis of a forum feature.

    Even within challenges, you will find that some challenges have attracted more replies than others. The reason is people find something interesting or relevant to share.

    Here's a story that I like:
    http://www.inspirationalstories.com/2/226.html
    Likes are like stars and thankfully there are no dots (or my posts would bag some :)).

    I try not to look at the stars mostly but then I am just another Wemmick:DD
    bobhc likes this.
  10. bines53

    bines53 Active Member

    Messages:
    641
  11. cooper

    cooper New Member

    Messages:
    2
    Just perfect! Thanks!

Share This Page