Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 215
  • Last Modified:

Excel 2010 - Mode.eq generating wierd results

I have a worksheet with four  instances of 25, three instances of "3" and two instances of '5'
Why am I getting nothing but "25" as my result?
Excel doesn't seem to like four instances of numbers?
(see attached workbook)
mode.xlsx
0
brothertruffle880
Asked:
brothertruffle880
  • 2
1 Solution
 
Rgonzo1971Commented:
Hi,

It is because it returns only the numbers with the max Count if you have 4 times 3, you would get 25 and 3 because both have the most Count of all.

Regards
0
 
barry houdiniCommented:
Rgonzo1971, is correct, you won't get a list of the most common numbers, only a list of the numbers with max count, in this case 25 only.

If you want a list showing 25, 3, 5 (the most common numbers that occur more than once, in order of numbers of occurences) then you can do this:

In A12

=IFERROR(MODE(A1:A11),"")

and then in A13

=IFERROR(MODE(IF(COUNTIF(A$12:A12,A$1:A$11)=0,A$1:A$11)),"")

confirmed with CTRL+SHIFT+ENTER and copied down as far as you want, when you run out of numbers you get blanks

regards, barry
0
 
brothertruffle880Author Commented:
Yikes.  I'm not understanding either of your explanation.
Can either of you elaborate on what is happening to my model?
I'm trying to understand the usage of the mode.eq function.  ?
I thought mode was supposed to give me 25, 3 and 5.  

Do I have a fundamental misunderstanding the operation of that function?
0
 
barry houdiniCommented:
It's MODE.MULT function, I believe, not MODE.EQ

That function will only give you multiple values if you have more than one number tied for the maximum number of appearances, so in your data, where there are 4 x 25 but no other numbers that repeat 4 times, the formula only returns 25

If you had these numbers

2, 5, 2, 5, 2, 5, 8, 2, 5, 8, 8

then the maximum number of appearances is 4, and both 2 and 5 appear 4 times so you get {2,5} returned by MODE.MULT......but you don't get 8 because 8 appears fewer than 4 times.

To get 25, 3 and 5 you need to use the formulas I suggested, see attached

regards, barry
MODEMULT.xlsx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now