Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rank/Large Help
Hello
I have a list of randomly repeating values in A1:A100. I would like a formula in B1:B10 that will return the top 10 used entries in the list in order of use highest to lowest. It is possible that there may be less than 10 used entries and in that case I would like a 0. Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rank/Large Help
Should B1 contain the value that appears most frequently in column A or the
largest value in column A?? -- Gary''s Student - gsnu200753 "Sandy" wrote: Hello I have a list of randomly repeating values in A1:A100. I would like a formula in B1:B10 that will return the top 10 used entries in the list in order of use highest to lowest. It is possible that there may be less than 10 used entries and in that case I would like a 0. Thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rank/Large Help
The value that appears most frequently
Thanks "Gary''s Student" wrote: Should B1 contain the value that appears most frequently in column A or the largest value in column A?? -- Gary''s Student - gsnu200753 "Sandy" wrote: Hello I have a list of randomly repeating values in A1:A100. I would like a formula in B1:B10 that will return the top 10 used entries in the list in order of use highest to lowest. It is possible that there may be less than 10 used entries and in that case I would like a 0. Thanks! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rank/Large Help
Let's start with B2 rather than B1
In B2 enter: =MODE(A1:A100) In B3 enter: =MODE(IF(ISNA(MATCH(A$1:A$100,B$2:B2,0)),A$1:A$100 )) this is an array formula inserted with CNTRL-SHFT-ENTER rather than ENTER. Copy B3 downwards. -- Gary''s Student - gsnu200753 "Sandy" wrote: The value that appears most frequently Thanks "Gary''s Student" wrote: Should B1 contain the value that appears most frequently in column A or the largest value in column A?? -- Gary''s Student - gsnu200753 "Sandy" wrote: Hello I have a list of randomly repeating values in A1:A100. I would like a formula in B1:B10 that will return the top 10 used entries in the list in order of use highest to lowest. It is possible that there may be less than 10 used entries and in that case I would like a 0. Thanks! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rank/Large Help
I am sorry I may have left out something important. The values are text and
I am getting N/A with text but if I use numbers it appears to work. Thanks "Gary''s Student" wrote: Let's start with B2 rather than B1 In B2 enter: =MODE(A1:A100) In B3 enter: =MODE(IF(ISNA(MATCH(A$1:A$100,B$2:B2,0)),A$1:A$100 )) this is an array formula inserted with CNTRL-SHFT-ENTER rather than ENTER. Copy B3 downwards. -- Gary''s Student - gsnu200753 "Sandy" wrote: The value that appears most frequently Thanks "Gary''s Student" wrote: Should B1 contain the value that appears most frequently in column A or the largest value in column A?? -- Gary''s Student - gsnu200753 "Sandy" wrote: Hello I have a list of randomly repeating values in A1:A100. I would like a formula in B1:B10 that will return the top 10 used entries in the list in order of use highest to lowest. It is possible that there may be less than 10 used entries and in that case I would like a 0. Thanks! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rank/Large Help
I found A solution. I used ahelpr column with countif and then applied your
formula to that column and then a vlookup with the results of your formula. Is there a better way to accomplish this? Thanks!!!! "Gary''s Student" wrote: Let's start with B2 rather than B1 In B2 enter: =MODE(A1:A100) In B3 enter: =MODE(IF(ISNA(MATCH(A$1:A$100,B$2:B2,0)),A$1:A$100 )) this is an array formula inserted with CNTRL-SHFT-ENTER rather than ENTER. Copy B3 downwards. -- Gary''s Student - gsnu200753 "Sandy" wrote: The value that appears most frequently Thanks "Gary''s Student" wrote: Should B1 contain the value that appears most frequently in column A or the largest value in column A?? -- Gary''s Student - gsnu200753 "Sandy" wrote: Hello I have a list of randomly repeating values in A1:A100. I would like a formula in B1:B10 that will return the top 10 used entries in the list in order of use highest to lowest. It is possible that there may be less than 10 used entries and in that case I would like a 0. Thanks! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Rank/Large Help
Is there a better way to accomplish this?
Depends on ones definition of better! rng = A1:A100 = text entries (no empty cells within the range!) Enter this array formula** in D1: =IF(COUNTA(rng)<10,0,INDEX(rng,MODE(MATCH(rng,rng, 0)+{0,0}))) Enter this array formula** D2 and copy down as needed: =IF(COUNTA(rng)<10,0,INDEX(rng,MODE(IF(COUNTIF(D$1 :D1,rng)=0,MATCH(rng,rng,0)+{0,0})))) Both formulas will return non-modal entries. That is, unique entries. For example: brown blue brown red red The formulas will return this array in order: brown red blue ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Sandy" wrote in message ... I found A solution. I used ahelpr column with countif and then applied your formula to that column and then a vlookup with the results of your formula. Is there a better way to accomplish this? Thanks!!!! "Gary''s Student" wrote: Let's start with B2 rather than B1 In B2 enter: =MODE(A1:A100) In B3 enter: =MODE(IF(ISNA(MATCH(A$1:A$100,B$2:B2,0)),A$1:A$100 )) this is an array formula inserted with CNTRL-SHFT-ENTER rather than ENTER. Copy B3 downwards. -- Gary''s Student - gsnu200753 "Sandy" wrote: The value that appears most frequently Thanks "Gary''s Student" wrote: Should B1 contain the value that appears most frequently in column A or the largest value in column A?? -- Gary''s Student - gsnu200753 "Sandy" wrote: Hello I have a list of randomly repeating values in A1:A100. I would like a formula in B1:B10 that will return the top 10 used entries in the list in order of use highest to lowest. It is possible that there may be less than 10 used entries and in that case I would like a 0. Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
LARGE / RANK FORMULA ERROR | Excel Discussion (Misc queries) | |||
Having Trouble with the RANK Function...Try LARGE or SMALL Functio | Excel Worksheet Functions | |||
RANK, duplicate ranking but no gaps in rank | Excel Worksheet Functions | |||
Rank where lowest value is highest rank | Excel Worksheet Functions | |||
Rank items, select one start date, have remaining dates follow based on rank | Excel Discussion (Misc queries) |