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

--
Message posted via http://www.officekb.com

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.

--
Biff
Microsoft Excel MVP


"wilchong via OfficeKB.com" <u43231@uwe wrote in message
news:93af384d7849c@uwe...
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

--
Message posted via http://www.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

T. Valko wrote:
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200903/1

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


"wilchong via OfficeKB.com" <u43231@uwe wrote in message
news:93b0bb5ac784c@uwe...
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

T. Valko wrote:
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200903/1

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





T. Valko wrote:
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))

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

--
Message posted via http://www.officekb.com

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


"wilchong via OfficeKB.com" <u43231@uwe wrote in message
news:93b17ae4deb83@uwe...
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





T. Valko wrote:
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))

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

--
Message posted via http://www.officekb.com