# What are 5 numbers in the highest frequency,

#### bines53

##### Active Member
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

#### Attachments

• 24 KB Views: 53

#### John Jairo V

##### Well-Known Member
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!

#### XOR LX

##### Active Member
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

##### Well-Known Member
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/unique-ordered-list-of-most-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.

#### bines53

##### Active Member
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:

#### bines53

##### Active Member
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

#### shrivallabha

##### Excel Ninja
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

#### cooper

##### New Member
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!
Just perfect! Thanks!