Dealing with ties in the Mode Function

DRJ

MrExcel MVP

Joined

Feb 17, 2002

Messages

3,853

• Mar 14, 2004

• #2

If there is a tie the Mode() function will return the first value it encountered in this case 7. If the numbers were

9
7
7
7
9
9

It would return 9.

If you want multiple values you would probably need to make your own function.

 

Upvote 0

S

Stephen_IV

Well-known Member

Joined

Mar 17, 2003

Messages

1,168

Office Version

1. 365
2. 2019

Platform

1. Windows

• Mar 14, 2004

• #3

I understand that. That is why I am asking for help?

 

Upvote 0

Y

Yogi Anand

MrExcel MVP

Joined

Mar 12, 2002

Messages

11,454

• Mar 14, 2004

• #4

Hi Stephen:

I recall we dealt recently with some questios regarding 1st mode, and 2nd mode, etc. Anyway, let us have a look at ...

Book1
	A	B	C	D	E
1	2		7
2	3		8	8
3	4
4	1
5	7
6	8
7	5
8	7
9	8
Sheet1

Cell C1 hoses the 1st mode, and cells C2 and D2, the light Turquoise and light yellow colored cells house two alternate formulations for the 2nd mode.

Is this what you are looking for?

 

Upvote 0

DRJ

MrExcel MVP

Joined

Feb 17, 2002

Messages

3,853

• Mar 14, 2004

• #5

Just to add a bit. Those are both array formulas you when you enter them press (control + shift + enter) and not just enter to make them work.

 

Upvote 0

N

nbrcrunch

Well-known Member

Joined

Jan 1, 2003

Messages

2,062

• Mar 14, 2004

• #6

When you have a notion of what (multiple) results you are expecting, you can use countif() to to return the count of each value you are expecting. In a long sequence of entries this may be not be feasible.

couple that with a column of: small(

#VALUE!

 

Upvote 0

A

acw

MrExcel MVP

Joined

Feb 13, 2004

Messages

4,814

• Mar 14, 2004

• #7

HI

A UDF. Note that it will return the #VALUE! error message if there is no mode.

Tony

Function moder(x As Range)
Dim modes As New Collection

For Each ce In x
If WorksheetFunction.CountIf(x, ce) = WorksheetFunction.CountIf(x, WorksheetFunction.Mode(x)) Then
On Error Resume Next
modes.Add Item:=ce, key:=Str(ce)
End If
Next

For i = 1 To modes.Count
moder = moder & "," & modes(i)
Next i
moder = WorksheetFunction.Substitute(moder, ",", "", 1)
End Function

 

Upvote 0

S

Stephen_IV

Well-known Member

Joined

Mar 17, 2003

Messages

1,168

Office Version

1. 365
2. 2019

Platform

1. Windows

• Mar 14, 2004

• #8

Thanks Tony and Yogi. Tony thats the approach that I was looking for! Thank you also Yogi for your time as well as nbrcrunch and DRJ

 

Upvote 0