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

Formulas and Functions

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

Diamond Contributor

Jul 17, 2020

You are welcome, glad to help

ErwinVanLoo
Copper Contributor
Jul 19, 2020
Hello SergeiBaklan

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
- Detlef_Lewin
  Silver Contributor
  Jul 19, 2020
  ErwinVanLoo
  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.
  - ErwinVanLoo
    Copper Contributor
    Jul 19, 2020
    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

Resources