Solved

Excel 2010 - Mode.eq generating wierd results

Posted on 2013-12-07
4
178 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 49

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 500 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

Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

831 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