Subsequent MODE
I know that I can use the MODE function to get the most occuring value
in a range; but is there a way to list the next 9 most occuring values in that range? Thanks in advance Mark |
Subsequent MODE
Try these:
Assume your data is in the range A2:A100 Enter this formula in C2: =MODE(A2:A100) Enter this array formula** in C3 and copy down as needed: =MODE(IF(COUNTIF(C$2:C2,A$2:A$100)=0,A$2:A$100)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Mark" wrote in message ... I know that I can use the MODE function to get the most occuring value in a range; but is there a way to list the next 9 most occuring values in that range? Thanks in advance Mark |
Subsequent MODE
On Nov 16, 12:46 pm, "T. Valko" wrote:
Try these: Assume your data is in the range A2:A100 Enter this formula in C2: =MODE(A2:A100) Enter this array formula** in C3 and copy down as needed: =MODE(IF(COUNTIF(C$2:C2,A$2:A$100)=0,A$2:A$100)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Mark" wrote in message ... I know that I can use the MODE function to get the most occuring value in a range; but is there a way to list the next 9 most occuring values in that range? Thanks in advance Mark- Hide quoted text - - Show quoted text - Thank you very much, It worked like a charm |
Subsequent MODE
"Mark" wrote in message
... On Nov 16, 12:46 pm, "T. Valko" wrote: Try these: Assume your data is in the range A2:A100 Enter this formula in C2: =MODE(A2:A100) Enter this array formula** in C3 and copy down as needed: =MODE(IF(COUNTIF(C$2:C2,A$2:A$100)=0,A$2:A$100)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Mark" wrote in message ... I know that I can use the MODE function to get the most occuring value in a range; but is there a way to list the next 9 most occuring values in that range? Thanks in advance Mark- Hide quoted text - - Show quoted text - Thank you very much, It worked like a charm You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP |
All times are GMT +1. The time now is 10:22 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com