get most frequent number mode.mult give me same number

Here's one way:

=INDEX($A$1:$A$17,MODE(MATCH($A$1:$A$17,$A$1:$A$17,0)))

This will give you the most frequently occurring number.

If this isn't what you want, then show us your expected outcomes.

If I'm reading this correctly, you want the commonest number, the next commonest, etc. See the sheet.

Commonest
=MODE($A$1:$A$20)

Next commonest:
=MODE(IF($A$1:$A$20<>D2,$A$1:$A$20))
entered as an array

Next Commonest:
=MODE(IF($A$1:$A$20<>D2,IF($A$1:$A$20<>E2,$A$1:$A$20)))
entered as an array.

And so on...

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

Hi,

Glenn's solution is along the right lines, though obviously could result in some quite lengthy formulas should the number of desired returns be quite large. What's more, it will only return values which occur at least twice.

I would prefer (again, assuming a range of A1:A20):

In C1:

=MODE.MULT(A1:A20)

In C2, array formula**:

=MODE(IF(COUNTIF(C$1:C1,A$1:A$20)={0,0},A$1:A$20))

and copied down.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Why don't you just copy the formula to cover six cells?

Regards

Nice, one XOR LX. One Q though... What's the advantage of {0,0} over just plain old 0 ?

Originally Posted by Glenn Kennedy

Nice, one XOR LX. One Q though... What's the advantage of {0,0} over just plain old 0 ?

It means that all entries will occur at least twice within the resulting 2-column array, hence ensuring that MODE does not error.

Cheers

Yep. i could see that excel was repeating the array when it evaluated the {0,0} bit. I wasn't clear why you wanted it to do that. Now I understand. However, it raises another Q in my head. Is it mathematically correct to return values that only occur once when looking for modes?? I suppose it depends on the intended purpose... thanks, anyhow.

Originally Posted by Glenn Kennedy

Is it mathematically correct to return values that only occur once when looking for modes??

More philosophical than mathematical, I suppose. Though obviously Microsoft agreed with you, else they wouldn't have designed the MODE functions that way.

Perhaps this particular case is not a very good example, though there are many cases in which it is necessary to manipulate the array returned from e.g. MODE.MULT in such a way that all entries are considered.

Regards