|
Formular for the SECOND highest frequency
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 |
Formular for the SECOND highest frequency
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 |
Formular for the SECOND highest frequency
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 |
Formular for the SECOND highest frequency
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 |
Formular for the SECOND highest frequency
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 |
Formular for the SECOND highest frequency
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 |
Formular for the SECOND highest frequency
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 T. Valko wrote: 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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200903/1 |
Formular for the SECOND highest frequency
Ok, good deal. Thanks for the feedback!
-- Biff Microsoft Excel MVP "wilchong via OfficeKB.com" <u43231@uwe wrote in message news:93b1e1f135352@uwe... 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 T. Valko wrote: 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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200903/1 |
Formular for the SECOND highest frequency
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 T. Valko wrote: 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] 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 |
| All times are GMT +1. The time now is 11:24 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com