Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Second highest frequency
Dear sir,
I have a series of number from A1:H1 (7,3,7,1,6,8,3,7). I need an Excel formular to describe the phenomenon of this particular series number: the frequency. If I want to to know the highest frequency number, the formula of MODE can give me the answer which is 7. However, I also need an formular which can give me answer: 3, because 3 appears two times in the series. The formular MODE only will give me the highest frequent number, that is 7. So, I don't know is there any formular can detect the second highest frequent number. Please advice. Wilchong -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200903/1 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Second highest frequency
It depends on exactly what you want. If you want *only* modal frequencies:
Try this: For the 2nd mode try this array formula** : =MODE(IF(A1:H1<MODE(A1:H1),A1:H1)) If there isn't a 2nd mode you'll get a #N/A error. For all modes and non-modals try this: A3 = your MODE(...) formula Enter this array formula** in A4 and copy down until you get #N/A errors: =MODE(IF(COUNTIF(A$3:A3,A$1:H$1)=0,A$1:H$1+{0;0})) Also assuming no empty cells within your range A1:H1. ** 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. -- Biff Microsoft Excel MVP "wilchong via OfficeKB.com" <u43231@uwe wrote in message news:9325b01bce83c@uwe... Dear sir, I have a series of number from A1:H1 (7,3,7,1,6,8,3,7). I need an Excel formular to describe the phenomenon of this particular series number: the frequency. If I want to to know the highest frequency number, the formula of MODE can give me the answer which is 7. However, I also need an formular which can give me answer: 3, because 3 appears two times in the series. The formular MODE only will give me the highest frequent number, that is 7. So, I don't know is there any formular can detect the second highest frequent number. Please advice. Wilchong -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200903/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Highest, Second Highest , Third Highest and so on | Excel Discussion (Misc queries) | |||
Display the Highest, Second Highest, Third Highest and so on... | Excel Discussion (Misc queries) | |||
2 rows, highest No in row 1, then highest number in row 2 relating to that column, possible duplicates | Excel Worksheet Functions | |||
second highest value | Excel Discussion (Misc queries) | |||
histograms - frequency and relative frequency? | Excel Discussion (Misc queries) |