I know that I can find the most common value in a group of numbers with MODE
function. But is this possible to find the second most common value in the
same group of numbers. It mustn't be with the same function, I just want to
get 3 most common values from one and the same group of numbers so that i
dont have to delete any numbers. I mean not so that i find the most common
value, delete all those values and then will get second most common value.
No. I want all numbers to stay.

Yes, it is possible to find the second most common value in a group of numbers in Excel. Here's how you can do it:

1. First, select the range of cells that contains the numbers you want to analyze.
2. Next, go to the "Formulas" tab in the ribbon and click on "More Functions" "Statistical" "FREQUENCY".
3. In the "FREQUENCY" dialog box, select the range of cells that contains the numbers you want to analyze as the "Data_array".
4. Then, select an empty range of cells where you want to display the frequency distribution as the "Bins_array". You can do this by clicking on the range selector button next to the "Bins_array" field and then selecting the range of cells.
5. Make sure that the "Array" option is checked and click "OK".
6. Excel will now display the frequency distribution in the range of cells you selected in step 4. The first column will show the values in the range, and the second column will show the frequency of each value.
7. To find the second most common value, simply look for the value with the second highest frequency in the frequency distribution. You can do this manually by scanning the second column, or you can use the "LARGE" function to automate the process. For example, if the frequency distribution is in cells A1:B10, you can use the following formula to find the second most common value:
   Formula:

   =LARGE(B1:B10,2) 


This formula will return the second largest value in the range B1:B10, which corresponds to the second most common value in the original range of cells. You can adjust the range references in the formula to match your specific data.

Try this...

Numbers in the range A2:A11

B2 = mode formula: =MODE(A2:A11)

Enter this array formula** in B3 and copy down as needed:

=MODE(IF(COUNTIF(B$2:B2,A$2:A$11)=0,A$2:A$11+{0,0} ))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Skalp" wrote in message
...
I know that I can find the most common value in a group of numbers with
MODE
function. But is this possible to find the second most common value in the
same group of numbers. It mustn't be with the same function, I just want
to
get 3 most common values from one and the same group of numbers so that i
dont have to delete any numbers. I mean not so that i find the most common
value, delete all those values and then will get second most common value.
No. I want all numbers to stay.

=MODE(IF(H1:H7<MODE(H1:H7),H1:H7))

this is an array formula, so commit it with Shift-Ctlr-Enter

--
__________________________________
HTH

Bob

"Skalp" wrote in message
...
I know that I can find the most common value in a group of numbers with
MODE
function. But is this possible to find the second most common value in the
same group of numbers. It mustn't be with the same function, I just want
to
get 3 most common values from one and the same group of numbers so that i
dont have to delete any numbers. I mean not so that i find the most common
value, delete all those values and then will get second most common value.
No. I want all numbers to stay.

Thank You very much, it works! And with that I can find the third most common
number too! It was really helpful!


"T. Valko" wrote:

Try this...

Numbers in the range A2:A11

B2 = mode formula: =MODE(A2:A11)

Enter this array formula** in B3 and copy down as needed:

=MODE(IF(COUNTIF(B$2:B2,A$2:A$11)=0,A$2:A$11+{0,0} ))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Skalp" wrote in message
...
I know that I can find the most common value in a group of numbers with
MODE
function. But is this possible to find the second most common value in the
same group of numbers. It mustn't be with the same function, I just want
to
get 3 most common values from one and the same group of numbers so that i
dont have to delete any numbers. I mean not so that i find the most common
value, delete all those values and then will get second most common value.
No. I want all numbers to stay.

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Skalp" wrote in message
...
Thank You very much, it works! And with that I can find the third most
common
number too! It was really helpful!


"T. Valko" wrote:

Try this...

Numbers in the range A2:A11

B2 = mode formula: =MODE(A2:A11)

Enter this array formula** in B3 and copy down as needed:

=MODE(IF(COUNTIF(B$2:B2,A$2:A$11)=0,A$2:A$11+{0,0} ))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Skalp" wrote in message
...
I know that I can find the most common value in a group of numbers with
MODE
function. But is this possible to find the second most common value in
the
same group of numbers. It mustn't be with the same function, I just
want
to
get 3 most common values from one and the same group of numbers so that
i
dont have to delete any numbers. I mean not so that i find the most
common
value, delete all those values and then will get second most common
value.
No. I want all numbers to stay.

Am I just not putting the information in correctly for this?

=MODE(IF(COUNTIF(L$8:L8,C$1:C$1577)=0,C$1:C$1577+{ 0,0}))

I have the mode in cell L8 and my data im trying to get the 2nd, 3rd, and 4th instead of just mode is in cells c1:c1577

Thanks in advance

EggHeadCafe - .NET Developer Portal of Choice
http://www.eggheadcafe.com/default.aspx?ref=ng