Forum Discussion

Copper Contributor

Jul 16, 2020

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

Modus question for Microsoft.xlsx12 KB

excel

Formulas and Functions

SergeiBaklan

Diamond Contributor

Jul 16, 2020

ErwinVanLoo

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

Modus question for Microsoft.xlsx19 KB

ErwinVanLoo
Copper Contributor
Jul 16, 2020
SergeiBaklan

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?
- SergeiBaklan
  Diamond Contributor
  Jul 16, 2020
  ErwinVanLoo
  
  That could be
  
  =TEXTJOIN(", ",1,MODE.MULT(A2:F2))
  Modus question for Microsoft.xlsx19 KB
  - ErwinVanLoo
    Copper Contributor
    Jul 17, 2020
    Hello SergeiBaklan,
    
    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