Formular for the SECOND highest frequency

[wilchong via OfficeKB.com]

Dear sir,
I have an excel formular which involve 5 cells, A1, B1, C1, D1 and E1. The
value in cell from A1 to E1 is 2, 6, 2, 2 and 6.
In the cell of H1, I place an Excel formular, MODE(A1:E1) and the result
shown "2", which mean "2" is the value that occurs the most frequently in my
data set.

My question is that I want to know is there other excel formular which can
shown the SECOND highest frequency ? In my example, "6" is the value that
occurs the SECOND most frequently.

I hope you can advice me on this. Many thanks,
Wilchong

 

[T. Valko]

One way...

Array entered** :

=MODE(IF(A1:E1<>H1,A1:E1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

If there isn't a "2nd mode" the formula will return #N/A.

 

[wilchong via OfficeKB.com]

Dear Mr. Valko,
Many thanks for your suggestion. However, the result showing "0" after array
entered the formula. How come it will happen in this way?

Thanks,
Wilchong

One way...

Array entered** :

=MODE(IF(A1:E1<>H1,A1:E1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

If there isn't a "2nd mode" the formula will return #N/A.

Dear sir,
I have an excel formular which involve 5 cells, A1, B1, C1, D1 and E1. The

[quoted text clipped - 11 lines]

I hope you can advice me on this. Many thanks,
Wilchong

 

[T. Valko]

You must have empty cells in your range.

To account for empty cells...(still array entered):

=MODE(IF((A1:E1<>H1)*(A1:E1<>""),A1:E1))

--
Biff
Microsoft Excel MVP

Dear Mr. Valko,
Many thanks for your suggestion. However, the result showing "0" after
array
entered the formula. How come it will happen in this way?

Thanks,
Wilchong

One way...

Array entered** :

=MODE(IF(A1:E1<>H1,A1:E1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

If there isn't a "2nd mode" the formula will return #N/A.

Dear sir,
I have an excel formular which involve 5 cells, A1, B1, C1, D1 and E1.
The

[quoted text clipped - 11 lines]

I hope you can advice me on this. Many thanks,
Wilchong

 

[wilchong via OfficeKB.com]

Dear Mr. Valko,
I think I am very stupid. I place the formula: =MODE(IF((A1:E1<>H1)*(A1:
E1<>""),A1:E1)) in the cell of H1, the result still the same, return a “0â€.
Of course, I entered the formula with array key!

When you said “I must have empty cells in the rangeâ€, what is actually mean
when I place the formula ?


Very sorry for my ignorant!

Many thanks,
Wilchong

You must have empty cells in your range.

To account for empty cells...(still array entered):

Dear Mr. Valko,
Many thanks for your suggestion. However, the result showing "0" after

[quoted text clipped - 23 lines]

 

[T. Valko]

If you entered the formula in H1 then you'd get a circular reference
warning. If you cancel the warning then you'd get a result of 0.

DON'T ENTER THE FORMULA IN CELL H1.

--
Biff
Microsoft Excel MVP

Dear Mr. Valko,
I think I am very stupid. I place the formula: =MODE(IF((A1:E1<>H1)*(A1:
E1<>""),A1:E1)) in the cell of H1, the result still the same, return a "0".
Of course, I entered the formula with array key!

When you said "I must have empty cells in the range", what is actually
mean
when I place the formula ?


Very sorry for my ignorant!

Many thanks,
Wilchong

You must have empty cells in your range.

To account for empty cells...(still array entered):

Dear Mr. Valko,
Many thanks for your suggestion. However, the result showing "0" after

[quoted text clipped - 23 lines]

I hope you can advice me on this. Many thanks,
Wilchong

 

[wilchong via OfficeKB.com]

Dear Mr. Valko
I placed your suggested Excel formula in the cell of H20. The result showing
"6". It is exactly what I want. I finally understand implication of the
formula!

Many thanks,
Wilson

If you entered the formula in H1 then you'd get a circular reference
warning. If you cancel the warning then you'd get a result of 0.

DON'T ENTER THE FORMULA IN CELL H1.

Dear Mr. Valko,
I think I am very stupid. I place the formula: =MODE(IF((A1:E1<>H1)*(A1:

[quoted text clipped - 21 lines]

 

[T. Valko]

Ok, good deal. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

Dear Mr. Valko
I placed your suggested Excel formula in the cell of H20. The result
showing
"6". It is exactly what I want. I finally understand implication of the
formula!

Many thanks,
Wilson

If you entered the formula in H1 then you'd get a circular reference
warning. If you cancel the warning then you'd get a result of 0.

DON'T ENTER THE FORMULA IN CELL H1.

Dear Mr. Valko,
I think I am very stupid. I place the formula:
=MODE(IF((A1:E1<>H1)*(A1:

[quoted text clipped - 21 lines]

I hope you can advice me on this. Many thanks,
Wilchong

 

[wilchong via OfficeKB.com]

Actually your first formular also can be worked, but one key point, I didn't
realised that the highest frequency value should be shown in the cell of H1
first. As a result, your formular show "2", which is the highest frequency
value.

Many thanks for your time!

Wilchong

Ok, good deal. Thanks for the feedback!

Dear Mr. Valko
I placed your suggested Excel formula in the cell of H20. The result

[quoted text clipped - 16 lines]