07-16-2020 07:34 AM
07-16-2020 07:34 AM
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?
07-16-2020 08:27 AM
To return few most frequent numbers, if they are, use MODE.MULT
07-16-2020 09:14 AM
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?
07-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.
07-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
07-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.
07-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?
07-19-2020 09:19 AM - edited 07-19-2020 11:09 AM
07-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")
=IFNA(TEXTJOIN(", ",1,MODE.MULT(A2:F2)),TEXTJOIN(", ",1,A2:F2))
or other variants. Result looks like