3 most common #'s

[jackf-nc]

Posted a message last week....And thought solution worked. But 2nd and 3rd most gives me errors.

Posted an example.
I want top 3 numbers from range in separate cells.
Used: =SMALL(MODE.MULT(IF(F2:F38>=i15, A2:E38)), {1})
(2} for second #
{3} for 3rd #

Some reason I get #num! error for 2nd and 3rd????

[shg]

MODE.MULT only returns multiple values if there are multiple values having equal counts, e.g., {1,1,2,2,3}\

Row\Col	I	J
1	23-Mar	I1: Input
2	46	I2: =MODE(IF($F$2:$F$25 >= I1, $A$2:$E$25))
3	27	I3: {=MODE(IF($F$2:$F$25 >= I1, IF(COUNTIF(I$2:I2, $A$2:$E$25)=0, $A$2:$E$25)))}
4	38
5	58
6	23

[jackf-nc]

SHG,

Could you please edit my example.? I am not able to reproduce your results. Also dont know what input for l1 should be....I assume its date from l15.
Thank you my friend, I was hoping you would reply!!!!!!!!

[JohnTopley]

I1=Date (23/03/2016)

I2: =MODE(IF($F$2:$F$25 >= I1, $A$2:$E$25))

I3: =MODE(IF($F$2:$F$25 >= I1, IF(COUNTIF(I$2:I2, $A$2:$E$25)=0, $A$2:$E$25)))

Enter I3 with Ctrl+Shift+Enter

Copy down column I to get 3rd, 4th etc.

To validate:

in K2: =COUNTIF($A$2:$E$23,I2)

Copy down to get counts of results in I

[jackf-nc]

John,

I am trying to get most 5 common #'s from group. I used your original formula but it hiccups on #2. It always appears as 0. I have uploaded a revised example. Please tell me what I am doing wrong.
Thanks,
Jack

[JohnTopley]

See attached. Range must be limited to that containing the data.

Where there are many numbers with same frequency, they will "allocated" in the order in which they occur in the matrix .

[jackf-nc]

Any way to avoid this? Maybe an if statement for a8:a500 - Currently there is not 500 records, just set it as that for future as data increases twice a week.

[JohnTopley]

Try

Named Range "MyData" defined as

=OFFSET(Sheet1!$A$8,,,COUNTA(Sheet1!$A8:$A500),5)

Formulae

B4

=MODE(MyData)

C4

=MODE(IF(COUNTIF($B$4:B$4, MyData)=0, MyData,""))

Enter C4 with Ctrl+shifT+Enter and copy across

[JohnTopley]

..... I moved data in row 4 to the right i.e. A4 is blank.

Will still work if data is in A4:E4

[jackf-nc]

Something isnt working correctly... Do you still have example? Man I really do appreciate your help! This has had me pulling hair out! Thanks again!

[JohnTopley]

See attached ...

[jackf-nc]

THANK YOU John!

I had issue with the offset. I incorrectly set range. I have one last question. how can I dynamically sort those 5 cells from smallest to largest.

Currently the five mode #'s are on one sheet. I want to have them sorted on another (5 cells) sheet. Any ideas?

[JohnTopley]

Assume values are in Sheet1!A1:A5

then in your second sheet say C1:G1

in C1

=Large(Sheet1!A1:A5,5) smallest

in G1

=Large(Sheet1!A1:A5,1) largest

D1, E1 and F1 will be 4, 3 and 2

[jackf-nc]

Thank you!!!Works great! I learned something new!