Home |
Search |
Today's Posts |
#1
|
|||
|
|||
formula help, PLEASE!!!
What formula do you use to look at a spread sheet and find the most frequently used number besides a MODE function. The problem I am haveing is I have used a MODE function to find the most frequent number, but now I need to find the second most used number and also the thrid and fourth most used numbers. But if I try and use a MODE function I do not know how to write it to not keep returning the most frequent number over and over???? Can ANYONE show me how to write a formula to not look for a value that has all ready been found in an array as the most frequent, I need to find the 5 most common numbers in an spreadsheet, bottom line... Thanks -- SRT ------------------------------------------------------------------------ SRT's Profile: http://www.excelforum.com/member.php...o&userid=25536 View this thread: http://www.excelforum.com/showthread...hreadid=389747 |
#2
|
|||
|
|||
Try a Pivot Table.
With your range (include a header cell we will call mydata) drag mydata into both the "row" and "data" sections of the layout. Make sure the mydata in the "data" section is set to "Count of mydata". If not double click mydata and select count. The pivot tabl;e will give you a list and the number of times each appears in the range. Don Pistulka "SRT" wrote in message ... What formula do you use to look at a spread sheet and find the most frequently used number besides a MODE function. The problem I am haveing is I have used a MODE function to find the most frequent number, but now I need to find the second most used number and also the thrid and fourth most used numbers. But if I try and use a MODE function I do not know how to write it to not keep returning the most frequent number over and over???? Can ANYONE show me how to write a formula to not look for a value that has all ready been found in an array as the most frequent, I need to find the 5 most common numbers in an spreadsheet, bottom line... Thanks -- SRT ------------------------------------------------------------------------ SRT's Profile: http://www.excelforum.com/member.php...o&userid=25536 View this thread: http://www.excelforum.com/showthread...hreadid=389747 |
#3
|
|||
|
|||
This *array* formula will return the 2nd most used number:
=MODE(IF(A1:A100<MODE(A1:A100),A1:A100)) -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. NOW, beyond that is out of my league, but then ... there's always Harlan. Here's his formula for finding the nth most occurring number: I set this formula up to find the *third* most used number in the A1:A100 range: =INDEX(A1:A100,MATCH(LARGE(FREQUENCY(A1:A100,A1:A1 00)+(1-ROW(INDIRECT("1:"&( ROWS(A1:A100)+1)))/(ROWS(A1:A100)+1)),3),FREQUENCY(A1:A100,A1:A100)+( 1-ROW(I NDIRECT("1:"&(ROWS(A1:A100)+1)))/(ROWS(A1:A100)+1)),0)) Look for the "3", and substitute whatever number you're looking for. BTW, this *does* work, so if you have a problem with it , lookout for word wrap. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "SRT" wrote in message ... What formula do you use to look at a spread sheet and find the most frequently used number besides a MODE function. The problem I am haveing is I have used a MODE function to find the most frequent number, but now I need to find the second most used number and also the thrid and fourth most used numbers. But if I try and use a MODE function I do not know how to write it to not keep returning the most frequent number over and over???? Can ANYONE show me how to write a formula to not look for a value that has all ready been found in an array as the most frequent, I need to find the 5 most common numbers in an spreadsheet, bottom line... Thanks -- SRT ------------------------------------------------------------------------ SRT's Profile: http://www.excelforum.com/member.php...o&userid=25536 View this thread: http://www.excelforum.com/showthread...hreadid=389747 |
#4
|
|||
|
|||
p.s.
If you highlight the count column and sort it desending, the most common will be on top, the second most common will be next, the third next, etc. Don Pistulka "Don" wrote in message ... Try a Pivot Table. With your range (include a header cell we will call mydata) drag mydata into both the "row" and "data" sections of the layout. Make sure the mydata in the "data" section is set to "Count of mydata". If not double click mydata and select count. The pivot tabl;e will give you a list and the number of times each appears in the range. Don Pistulka "SRT" wrote in message ... What formula do you use to look at a spread sheet and find the most frequently used number besides a MODE function. The problem I am haveing is I have used a MODE function to find the most frequent number, but now I need to find the second most used number and also the thrid and fourth most used numbers. But if I try and use a MODE function I do not know how to write it to not keep returning the most frequent number over and over???? Can ANYONE show me how to write a formula to not look for a value that has all ready been found in an array as the most frequent, I need to find the 5 most common numbers in an spreadsheet, bottom line... Thanks -- SRT ------------------------------------------------------------------------ SRT's Profile: http://www.excelforum.com/member.php...o&userid=25536 View this thread: http://www.excelforum.com/showthread...hreadid=389747 |
#5
|
|||
|
|||
SRT wrote:
What formula do you use to look at a spread sheet and find the most frequently used number besides a MODE function. The problem I am haveing is I have used a MODE function to find the most frequent number, but now I need to find the second most used number and also the thrid and fourth most used numbers. But if I try and use a MODE function I do not know how to write it to not keep returning the most frequent number over and over???? Can ANYONE show me how to write a formula to not look for a value that has all ready been found in an array as the most frequent, I need to find the 5 most common numbers in an spreadsheet, bottom line... Thanks -------------------- If you're trying to find more than just the #1 and #2 most frequent numbers, there is a Histogram function provided with Excel. As I recall you have to install the Analysis Tookpak add-in that came with Excel. This can be set up to provide how many times each number in your data set occurs. To invoke it click on ToolsDataAnalysis and pick Histogram from the list. The Excel help system will describe the Histogram function and its options. Good luck... Bill |
#6
|
|||
|
|||
Hi RD!
I don't have anything better but one thing I ran across when I tried Harlans formula was that if you replace the 3 with ROW(1:1) and drag down to increment, after all the unique values have been returned according to their nth "mode", then the formula starts randomly repeating. You won't get an error return until you copy to enough cells that exceds the range size. Biff "Ragdyer" wrote in message ... This *array* formula will return the 2nd most used number: =MODE(IF(A1:A100<MODE(A1:A100),A1:A100)) -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. NOW, beyond that is out of my league, but then ... there's always Harlan. Here's his formula for finding the nth most occurring number: I set this formula up to find the *third* most used number in the A1:A100 range: =INDEX(A1:A100,MATCH(LARGE(FREQUENCY(A1:A100,A1:A1 00)+(1-ROW(INDIRECT("1:"&( ROWS(A1:A100)+1)))/(ROWS(A1:A100)+1)),3),FREQUENCY(A1:A100,A1:A100)+( 1-ROW(I NDIRECT("1:"&(ROWS(A1:A100)+1)))/(ROWS(A1:A100)+1)),0)) Look for the "3", and substitute whatever number you're looking for. BTW, this *does* work, so if you have a problem with it , lookout for word wrap. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "SRT" wrote in message ... What formula do you use to look at a spread sheet and find the most frequently used number besides a MODE function. The problem I am haveing is I have used a MODE function to find the most frequent number, but now I need to find the second most used number and also the thrid and fourth most used numbers. But if I try and use a MODE function I do not know how to write it to not keep returning the most frequent number over and over???? Can ANYONE show me how to write a formula to not look for a value that has all ready been found in an array as the most frequent, I need to find the 5 most common numbers in an spreadsheet, bottom line... Thanks -- SRT ------------------------------------------------------------------------ SRT's Profile: http://www.excelforum.com/member.php...o&userid=25536 View this thread: http://www.excelforum.com/showthread...hreadid=389747 |
#7
|
|||
|
|||
Also, using your revision makes it very obvious that ties don't fare too
well with the formula. The second, third and/or fourth ... etc. tie is never returned. -- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- "Biff" wrote in message ... Hi RD! I don't have anything better but one thing I ran across when I tried Harlans formula was that if you replace the 3 with ROW(1:1) and drag down to increment, after all the unique values have been returned according to their nth "mode", then the formula starts randomly repeating. You won't get an error return until you copy to enough cells that exceds the range size. Biff "Ragdyer" wrote in message ... This *array* formula will return the 2nd most used number: =MODE(IF(A1:A100<MODE(A1:A100),A1:A100)) -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. NOW, beyond that is out of my league, but then ... there's always Harlan. Here's his formula for finding the nth most occurring number: I set this formula up to find the *third* most used number in the A1:A100 range: =INDEX(A1:A100,MATCH(LARGE(FREQUENCY(A1:A100,A1:A1 00)+(1-ROW(INDIRECT("1:"&( ROWS(A1:A100)+1)))/(ROWS(A1:A100)+1)),3),FREQUENCY(A1:A100,A1:A100)+( 1-ROW(I NDIRECT("1:"&(ROWS(A1:A100)+1)))/(ROWS(A1:A100)+1)),0)) Look for the "3", and substitute whatever number you're looking for. BTW, this *does* work, so if you have a problem with it , lookout for word wrap. -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "SRT" wrote in message ... What formula do you use to look at a spread sheet and find the most frequently used number besides a MODE function. The problem I am haveing is I have used a MODE function to find the most frequent number, but now I need to find the second most used number and also the thrid and fourth most used numbers. But if I try and use a MODE function I do not know how to write it to not keep returning the most frequent number over and over???? Can ANYONE show me how to write a formula to not look for a value that has all ready been found in an array as the most frequent, I need to find the 5 most common numbers in an spreadsheet, bottom line... Thanks -- SRT ------------------------------------------------------------------------ SRT's Profile: http://www.excelforum.com/member.php...o&userid=25536 View this thread: http://www.excelforum.com/showthread...hreadid=389747 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
referencing named formula using INDIRECT function | Excel Worksheet Functions | |||
Simplify formula | Excel Worksheet Functions | |||
put formula results into a different cell if it is empty | Excel Worksheet Functions | |||
how do i write a formula and keep in in formula form, so it DOESN. | Excel Discussion (Misc queries) | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |