Mode-1

[DArinello]

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.

 

[Max]

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

where:

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.

 

[Mayank Prakash Gupta]

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

 

[Aladin Akyurek]

See:

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

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.

 

[Harlan Grove]

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.

 

[akyurek]

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?

 

[Harlan Grove]

(e-mail address removed) wrote...

Harlan Grove wrote: ....

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.