asked on # 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

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

Microsoft ExcelSpreadsheets

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

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

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

regards, barry

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?

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?

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

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