Solved

Excel 2010 - Mode.eq generating wierd results

Posted on 2013-12-07
4
185 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 50

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

808 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