Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel 2010 - Mode.eq generating wierd results

Posted on 2013-12-07
4
Medium Priority
?
202 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

670 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