 Highlighted

# Excel calculating modus is not always correct AGGREGATE(13;3;VALUES);

Hello,

Given are the values 37, 38, 38 ,39, 40, 40.

The aggregate function AGGREGATE(13;3;VALUES) returns 38

But the with the function table We see that the values 38 AND 40 are 2 times in the given values, So the values 38 and 40 are the two Modi ..... for the given values ...

And de aggregate gives only one of the two ... Why?

Isn't it better that if there are 2 or more Modi that the agregate function gives an error in stead of only give one f the two or more.?

How can we test in excel that there are two modi?

How can we get in excel all the modi?

Greetings

E.Van Loo

10 Replies
Highlighted

# Re: Excel calculating modus is not always correct AGGREGATE(13;3;VALUES);

To return few most frequent numbers, if they are, use MODE.MULT Highlighted

# Re: Excel calculating modus is not always correct AGGREGATE(13;3;VALUES);

@Sergei Baklan

Thanks,

Now I get the different modi in different cells.

How can I collect these values in one string to show them together in one cell?

Highlighted

# Re: Excel calculating modus is not always correct AGGREGATE(13;3;VALUES);

That could be ``=TEXTJOIN(", ",1,MODE.MULT(A2:F2))``
Highlighted

# Re: Excel calculating modus is not always correct AGGREGATE(13;3;VALUES);

Hello @Sergei Baklan,

It works pefect, thanks.

I have added the sort function in your formula to have the numbers always in a good numeric order.

Greetings

Erwin

Highlighted

# Re: Excel calculating modus is not always correct AGGREGATE(13;3;VALUES);

You are welcome, glad to help

Highlighted

# Re: Excel calculating modus is not always correct AGGREGATE(13;3;VALUES);

Hello @Sergei Baklan

When you take a few numbers, all different from each other for example 2 ; 15 ; 28 ; 34 ; 17 (so all the numbers are only mentionned once)

then every of the next formulas Aggregate ( 13;3;... ) ; MODE.SNGL(values) ; MODE.MULT(values) give errors, none of them gives the right answer i.e. 2 , 15, 28 , 34 , 17

Greetings

Erwin

Highlighted

# Re: Excel calculating modus is not always correct AGGREGATE(13;3;VALUES);

You should have taken a look at the MS support pages:

``````If the data set contains no duplicate data points, MODE.SNGL returns the #N/A error value.

If the data set contains no duplicate data points, MODE.MULT returns the #N/A error value.``````
Highlighted

# Re: Excel calculating modus is not always correct AGGREGATE(13;3;VALUES);

Hello @Detlef Lewin

Thanks for that information.

But the two functions are created to give the modus of numbers.

If all the numbers appears once in the list, they are all the modus.

So the fuctions aggregate,  MODE.SNGL(values) ; MODE.MULT(values), do not where they are designed for...

Where can I find the MS support pages?

Greetings

Erwin

Highlighted

# Re: Excel calculating modus is not always correct AGGREGATE(13;3;VALUES);

You can send feedback via Help -> Feedback.

For the support pages:

In the browser: https://support.microsoft.com/ and klick through the options.

In Excel: F1 or Help -> Help or click on the function name in the tooltip.

Highlighted

# Re: Excel calculating modus is not always correct AGGREGATE(13;3;VALUES);

How to interpret no duplicates, return all numbers or handle no most frequent number situation depends on concrete project. From this point of view Excel correctly returns an error which we could interpret as necessary. That could be

``=IFNA(TEXTJOIN(", ",1,MODE.MULT(A2:F2)),"no duplicates")``

or

``=IFNA(TEXTJOIN(", ",1,MODE.MULT(A2:F2)),TEXTJOIN(", ",1,A2:F2))``

or other variants. Result looks like 