Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel 2010 - Mode.eq generating wierd results

Posted on 2013-12-07
4
Medium Priority
?
208 Views
Last Modified: 2013-12-08
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
Comment
Question by:brothertruffle880
  • 2
4 Comments
 
LVL 53

Expert Comment

by:Rgonzo1971
ID: 39702954
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
 
LVL 50

Expert Comment

by:barry houdini
ID: 39703122
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
 

Author Comment

by:brothertruffle880
ID: 39703331
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
 
LVL 50

Accepted Solution

by:
barry houdini earned 2000 total points
ID: 39703381
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

879 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question