|  | Sorted Data | | |
| | | Norm |  |
|  Posted: Sun Aug 24, 2008 12:29 pm Post subject: Sorted Data |  |
I have columns that have quite a bit a bit of data in them and some of the
numbers are the same.. I would like to be able to find the number that
appears most often, then the second most appearing number and third, all the
way to the fifth most appearing number. I have been able to use "MODE" to
find the most often used number but have not be able to find out how to do
the rest. Any help would be appreciated. |
| |
| | | Ragdyer | |
| | Posted: Sun Aug 24, 2008 2:10 pm Post subject: Re: Sorted Data | |
With data in say A1 to A100, enter the frequency rating in B1,
and try this:
=INDEX(A1:A100,MATCH(LARGE(FREQUENCY(A1:A100,A1:A20),B1),FREQUENCY(A1:A100,A
1:A100),0))
--
HTH,
RD
---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Norm" <nwatt@ec.rr.com> wrote in message
news:u1NxNXfBJHA.528@TK2MSFTNGP06.phx.gbl...
| Quote: | I have columns that have quite a bit a bit of data in them and some of
the
numbers are the same.. I would like to be able to find the number that
appears most often, then the second most appearing number and third, all
the
way to the fifth most appearing number. I have been able to use "MODE" to
find the most often used number but have not be able to find out how to do
the rest. Any help would be appreciated.
|
|
| |
| | | Ragdyer | |
| | Posted: Sun Aug 24, 2008 2:15 pm Post subject: Re: Sorted Data | |
| |  | |
TYPO!
Try this one:
=INDEX(A1:A100,MATCH(LARGE(FREQUENCY(A1:A100,A1:A100),B1),FREQUENCY(A1:A100,
A1:A100),0))
--
Regards,
RD
---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Ragdyer" <RagDyer@cutoutmsn.com> wrote in message
news:Ozte2QgBJHA.3496@TK2MSFTNGP03.phx.gbl...
| Quote: | With data in say A1 to A100, enter the frequency rating in B1,
and try this:
=INDEX(A1:A100,MATCH(LARGE(FREQUENCY(A1:A100,A1:A20),B1),FREQUENCY(A1:A100,A
1:A100),0))
--
HTH,
RD
--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit !
--------------------------------------------------------------------------
-
"Norm" <nwatt@ec.rr.com> wrote in message
news:u1NxNXfBJHA.528@TK2MSFTNGP06.phx.gbl...
I have columns that have quite a bit a bit of data in them and some of
the
numbers are the same.. I would like to be able to find the number that
appears most often, then the second most appearing number and third, all
the
way to the fifth most appearing number. I have been able to use "MODE"
to
find the most often used number but have not be able to find out how to
do
the rest. Any help would be appreciated.
|
|
| |
| | | Andrew R | |
| | Posted: Mon Aug 25, 2008 1:28 am Post subject: Re: Sorted Data | |
On Aug 24, 10:29 am, "Norm" <nw...@ec.rr.com> wrote:
| Quote: | I have columns that have quite a bit a bit of data in them and some of the
numbers are the same.. I would like to be able to find the number that
appears most often, then the second most appearing number and third, all the
way to the fifth most appearing number. I have been able to use "MODE" to
find the most often used number but have not be able to find out how to do
the rest. Any help would be appreciated.
|
try using conditionl formatting to change the color of the cell then
you can sort on cell color |
| |
| | | Bernd P | |
| | Posted: Mon Aug 25, 2008 8:09 am Post subject: Re: Sorted Data | |
Hello,
You can use my UDF Lfreq and change the sort key (index 2, not 1) to
sort by frequency, not name.
LINK
Regards,
Bernd |
| |
| | | ThatSaid | |
| | Posted: Mon Aug 25, 2008 8:26 am Post subject: Re: Sorted Data | |
Easiest way is to use a Pivot table.
(Excuse me if some of the vocab is not "spot on" as I'm working with non
English version and I'm trying to remember the English terms.)
Name your column of numbers "MyNumbers".
Select a Cell in the column.
Then click
menu / data / Pivot table (or Insert Pivot table in XL 2007).
Slide the MyNumbers header from the list of headers into the rows box on the
left.
Slide the MyNumbers header from the list of headers (again) in to the data
box in the table body, but then modify it from Sum to Count.
Click OK, OK, OK until the pivot table appears.
Now you have the pivot table but sorted in the wrong order.
So, Right Click on a cell in the numbers column.
Edit settings ...
Sort, from greatest to least, on the Count of numbers.
Click OK.
OK ?
ThatSaid. |
| |
| | | Gord Dibben | |
| | Posted: Mon Aug 25, 2008 11:31 am Post subject: Re: Sorted Data | |
How would you go about sorting on cell color?
Gord Dibben MS Excel MVP
On Sun, 24 Aug 2008 18:28:54 -0700 (PDT), Andrew R
<arowland@apbspeakers.com> wrote:
| Quote: | try using conditionl formatting to change the color of the cell then
you can sort on cell color |
|
| |
| | | Bob I | |
| | Posted: Mon Aug 25, 2008 2:26 pm Post subject: Re: Sorted Data | |
Start with Excel 2007, and then pick, Home, Sort and filter, Custom
sort, pick Cell Color in Sort on.
Gord Dibben wrote:
| Quote: | How would you go about sorting on cell color?
Gord Dibben MS Excel MVP
On Sun, 24 Aug 2008 18:28:54 -0700 (PDT), Andrew R
arowland@apbspeakers.com> wrote:
try using conditionl formatting to change the color of the cell then
you can sort on cell color
|
|
| |
| | | Gord Dibben | |
| | Posted: Mon Aug 25, 2008 4:33 pm Post subject: Re: Sorted Data | |
Thanks Bob.
One of these days I may install 2007<g>
Gord
On Mon, 25 Aug 2008 11:26:05 -0500, Bob I <birelan@yahoo.com> wrote:
| Quote: | Start with Excel 2007, and then pick, Home, Sort and filter, Custom
sort, pick Cell Color in Sort on.
Gord Dibben wrote:
How would you go about sorting on cell color?
Gord Dibben MS Excel MVP
On Sun, 24 Aug 2008 18:28:54 -0700 (PDT), Andrew R
arowland@apbspeakers.com> wrote:
try using conditionl formatting to change the color of the cell then
you can sort on cell color
|
|
| |
| | | Bob I | |
| | Posted: Mon Aug 25, 2008 5:49 pm Post subject: Re: Sorted Data | |
Most welcome, although someone has to lay back to help the folks who
don't upgrade. <wink>
Gord Dibben wrote:
| Quote: | Thanks Bob.
One of these days I may install 2007<g
Gord
On Mon, 25 Aug 2008 11:26:05 -0500, Bob I <birelan@yahoo.com> wrote:
Start with Excel 2007, and then pick, Home, Sort and filter, Custom
sort, pick Cell Color in Sort on.
Gord Dibben wrote:
How would you go about sorting on cell color?
Gord Dibben MS Excel MVP
On Sun, 24 Aug 2008 18:28:54 -0700 (PDT), Andrew R
arowland@apbspeakers.com> wrote:
try using conditionl formatting to change the color of the cell then
you can sort on cell color
|
|
| |
|
|
