Mode-1
 

I need to figure something out on my sheet. The “mode” function tells
you witch value repeats the most. I want to see if there is another
value that repeats just as much. Also, I want to see what value
repeats the second most.

Ex:
4
6
4
7
5
6
4
5
5

If I use the function: =mode(list). It will return, “4”. By using
=countif((list),mode(list)) I see that that repeated 3 times.

I want a way to see that “5” also repeated 3 times and then that 6 is
repeated 2 times.


--
DArinello
------------------------------------------------------------------------
DArinello's Profile: http://www.excelforum.com/member.php...o&userid=25589
View this thread: http://www.excelforum.com/showthread...hreadid=390089

One way to play with ..

Assuming the sample data is in A1:A9

Put in:

B1:
=IF(COUNTIF($A$1:A1,A1)1,"",ROW())

C1:
=IF(ISERROR(SMALL(B:B,ROWS($A$1:A1))),"",INDEX(A:A ,MATCH(SMALL(B:B,ROWS($A$1
:A1)),B:B,0)))

D1:
=COUNTIF(A:A,C1)

E1:
=IF(D1=0,"",D1-ROW()/10^10)

F1:
=IF(ISERROR(LARGE($E:$E,ROWS($A$1:A1))),"",INDEX(C :C,MATCH(LARGE($E:$E,ROWS(
$A$1:A1)),$E:$E,0)))

Copy F1 across to G1

Select B1:G1, fill down to G9

Cols F & G will return the desired results:

4 3
5 3
6 2
7 1

whe

col F = unique numbers within source range A1:A9
col G = occurence count of the unique numbers

The listing in col F will be sorted in descending order by the counts in col
G. Ties, if any, will be listed in the same relative order that the unique
numbers appear in the source range.

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"DArinello" wrote
in message ...

I need to figure something out on my sheet. The “mode” function tells
you witch value repeats the most. I want to see if there is another
value that repeats just as much. Also, I want to see what value
repeats the second most.

Ex:
4
6
4
7
5
6
4
5
5

If I use the function: =mode(list). It will return, “4”. By using
=countif((list),mode(list)) I see that that repeated 3 times.

I want a way to see that “5” also repeated 3 times and then that 6 is
repeated 2 times.


--
DArinello
------------------------------------------------------------------------
DArinello's Profile:
http://www.excelforum.com/member.php...o&userid=25589
View this thread: http://www.excelforum.com/showthread...hreadid=390089

try using pivot tables...it will show the count of all the numbers


"DArinello" wrote
in message ...

I need to figure something out on my sheet. The "mode" function tells
you witch value repeats the most. I want to see if there is another
value that repeats just as much. Also, I want to see what value
repeats the second most.

Ex:
4
6
4
7
5
6
4
5
5

If I use the function: =mode(list). It will return, "4". By using
=countif((list),mode(list)) I see that that repeated 3 times.

I want a way to see that "5" also repeated 3 times and then that 6 is
repeated 2 times.


--
DArinello
------------------------------------------------------------------------
DArinello's Profile:
http://www.excelforum.com/member.php...o&userid=25589
View this thread: http://www.excelforum.com/showthread...hreadid=390089

See:

http://www.mrexcel.com/board2/viewtopic.php?t=159292

DArinello wrote:
I need to figure something out on my sheet. The “mode” function tells
you witch value repeats the most. I want to see if there is another
value that repeats just as much. Also, I want to see what value
repeats the second most.

Ex:
4
6
4
7
5
6
4
5
5

If I use the function: =mode(list). It will return, “4”. By using
=countif((list),mode(list)) I see that that repeated 3 times.

I want a way to see that “5” also repeated 3 times and then that 6 is
repeated 2 times.



--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

Aladin Akyurek wrote...
....
--
[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

Interesting signature, but the first would require SUMPRODUCT to work
differently than other functions with respect to boolean TRUE. If you
want that functionality, you could always switch to Gnumeric.

As for the second, it'd be nice if there were an error trapping
function or even better a general trapping function: a function that
would return the value of its 1st argument if it satisfied its 2nd
COUNTIF/SUMIF-like criteria argument or the value of its optional 3rd
argument otherwise, with the default value for the 3rd argument being
VBA Empty, so "" in text contexts or 0 in numeric contexts.

Still, I'd prefer eliminating the artificial 7 nested function call
limit and allow different files with the same base filename to be open
at the same time, no matter how much recoding would be necessary.

Harlan Grove wrote:
Aladin Akyurek wrote...
...
--
[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

Interesting signature, but the first would require SUMPRODUCT to work
differently than other functions with respect to boolean TRUE. If you
want that functionality, you could always switch to Gnumeric.

The proposal is SumProduct for it would be less desirable to have:

=SUM(1,2,TRUE)

to yield 4. Or

=MAX(0.2,0.4,TRUE)

to yield 1.


As for the second, it'd be nice if there were an error trapping
function or even better a general trapping function: a function that
would return the value of its 1st argument if it satisfied its 2nd
COUNTIF/SUMIF-like criteria argument or the value of its optional 3rd
argument otherwise, with the default value for the 3rd argument being
VBA Empty, so "" in text contexts or 0 in numeric contexts.


[...]

That would not pre-empt my proposal, would it?

wrote...
Harlan Grove wrote:
....
As for the second, it'd be nice if there were an error trapping
function or even better a general trapping function: a function that
would return the value of its 1st argument if it satisfied its 2nd
COUNTIF/SUMIF-like criteria argument or the value of its optional 3rd
argument otherwise, with the default value for the 3rd argument being
VBA Empty, so "" in text contexts or 0 in numeric contexts.

That would not pre-empt my proposal, would it?

I believe it would. It would also allow some chance of backward
compatibility. If TRAP were added to Excel XVI, formulas like

=TRAP(VLOOKUP(x,y,z,0),"<#*","no "&x&" found")

could be used in older versions if an add-in TRAP function were
provided, but

=VLOOKUP(x,y,z,0,"no "&x&" found")

would cause problems in older versions.

Now if Microsoft were to consider canibalizing their spreadsheet market
share and sell a new & different spreadsheet, then there are LOTS of
things to change. But I doubt that'd happen unless & until they lost
serious market share, and FWIW OpenOffice Calc isn't good enough yet,
and Gnumeric is too slow under Windows.