Jul 16 2020 07:34 AM
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
Jul 16 2020 08:27 AM
Jul 16 2020 09:14 AM
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?
Jul 16 2020 09:37 AM
Jul 17 2020 10:15 AM
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
Jul 17 2020 12:52 PM
You are welcome, glad to help
Jul 19 2020 03:12 AM
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
Jul 19 2020 05:12 AM
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.
Jul 19 2020 05:53 AM
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
Jul 19 2020 09:19 AM - edited Jul 19 2020 11:09 AM
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.
Jul 19 2020 10:21 AM
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