Forum Discussion
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 ...
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
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