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

Question

Hello,&nbsp;Given are the values 37, 38, 38 ,39, 40, 40.&nbsp;The aggregate function&nbsp;AGGREGATE(13;3;VALUES) returns 38But the with the function table&nbsp;&nbsp;&nbsp;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 ...&nbsp;And de aggregate gives only one of the two ... Why?&nbsp;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?&nbsp;GreetingsE.Van Loo&nbsp;&nbsp;&nbsp;

sergeibaklan · Answer

ErwinVanLoo&nbsp;
To return few most frequent numbers, if they are, use MODE.MULT

&nbsp;

erwinvanloo · Answer

SergeiBaklan&nbsp;Thanks,&nbsp;&nbsp;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?

sergeibaklan · Answer

ErwinVanLoo&nbsp;
That could be

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

erwinvanloo · Answer

Hello&nbsp;SergeiBaklan,&nbsp;It works pefect, thanks.I have added the sort function in your formula to have the numbers always in a good numeric order.&nbsp;GreetingsErwin&nbsp;

sergeibaklan · Answer

ErwinVanLoo&nbsp;
You are welcome, glad to help

Forum Discussion

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

10 Replies

Resources