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

%3CLINGO-SUB%20id%3D%22lingo-sub-1526625%22%20slang%3D%22en-US%22%3EExcel%20calculating%20modus%20is%20not%20always%20correct%20AGGREGATE(13%3B3%3BVALUES)%3B%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1526625%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGiven%20are%20the%20values%2037%2C%2038%2C%2038%20%2C39%2C%2040%2C%2040.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20aggregate%20function%26nbsp%3BAGGREGATE(13%3B3%3BVALUES)%20returns%2038%3C%2FP%3E%3CP%3EBut%20the%20with%20the%20function%20table%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22ErwinVanLoo_1-1594909985105.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F205805i2F65FAD3F4B66711%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22ErwinVanLoo_1-1594909985105.png%22%20alt%3D%22ErwinVanLoo_1-1594909985105.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWe%20see%20that%20the%20values%2038%20AND%2040%20are%202%20times%20in%20the%20given%20values%2C%20So%20the%20values%2038%20and%2040%20are%20the%20two%20Modi%20.....%20for%20the%20given%20values%20...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20de%20aggregate%20gives%20only%20one%20of%20the%20two%20...%20Why%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIsn't%20it%20better%20that%20if%20there%20are%202%20or%20more%20Modi%20that%20the%20agregate%20function%20gives%20an%20error%20in%20stead%20of%20only%20give%20one%20f%20the%20two%20or%20more.%3F%3C%2FP%3E%3CP%3EHow%20can%20we%20test%20in%20excel%20that%20there%20are%20two%20modi%3F%3C%2FP%3E%3CP%3EHow%20can%20we%20get%20in%20excel%20all%20the%20modi%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGreetings%3C%2FP%3E%3CP%3EE.Van%20Loo%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1526625%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1526793%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20calculating%20modus%20is%20not%20always%20correct%20AGGREGATE(13%3B3%3BVALUES)%3B%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1526793%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F730715%22%20target%3D%22_blank%22%3E%40ErwinVanLoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20return%20few%20most%20frequent%20numbers%2C%20if%20they%20are%2C%20use%20MODE.MULT%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20498px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F205821i08CAF5377D5D2B1A%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1526935%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20calculating%20modus%20is%20not%20always%20correct%20AGGREGATE(13%3B3%3BVALUES)%3B%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1526935%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3ENow%20I%20get%20the%20different%20modi%20in%20different%20cells.%3C%2FP%3E%3CP%3EHow%20can%20I%20collect%20these%20values%20in%20one%20string%20to%20show%20them%20together%20in%20one%20cell%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1527007%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20calculating%20modus%20is%20not%20always%20correct%20AGGREGATE(13%3B3%3BVALUES)%3B%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1527007%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F730715%22%20target%3D%22_blank%22%3E%40ErwinVanLoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20could%20be%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20476px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F205840iCF66DA21E8D53AC8%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DTEXTJOIN(%22%2C%20%22%2C1%2CMODE.MULT(A2%3AF2))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1529616%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20calculating%20modus%20is%20not%20always%20correct%20AGGREGATE(13%3B3%3BVALUES)%3B%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1529616%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20works%20pefect%2C%20thanks.%3C%2FP%3E%3CP%3EI%20have%20added%20the%20sort%20function%20in%20your%20formula%20to%20have%20the%20numbers%20always%20in%20a%20good%20numeric%20order.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGreetings%3C%2FP%3E%3CP%3EErwin%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1529979%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20calculating%20modus%20is%20not%20always%20correct%20AGGREGATE(13%3B3%3BVALUES)%3B%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1529979%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F730715%22%20target%3D%22_blank%22%3E%40ErwinVanLoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20are%20welcome%2C%20glad%20to%20help%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1531639%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20calculating%20modus%20is%20not%20always%20correct%20AGGREGATE(13%3B3%3BVALUES)%3B%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1531639%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20you%20take%20a%20few%20numbers%2C%20all%20different%20from%20each%20other%20for%20example%202%20%3B%2015%20%3B%2028%20%3B%2034%20%3B%2017%20(so%20all%20the%20numbers%20are%20only%20mentionned%20once)%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethen%20every%20of%20the%20next%20formulas%20Aggregate%20(%2013%3B3%3B...%20)%20%3B%26nbsp%3BMODE.SNGL(values)%20%3B%26nbsp%3BMODE.MULT(values)%20give%20errors%2C%20none%20of%20them%20gives%20the%20right%20answer%20i.e.%202%20%2C%2015%2C%2028%20%2C%2034%20%2C%2017%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGreetings%3C%2FP%3E%3CP%3EErwin%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1531735%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20calculating%20modus%20is%20not%20always%20correct%20AGGREGATE(13%3B3%3BVALUES)%3B%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1531735%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F730715%22%20target%3D%22_blank%22%3E%40ErwinVanLoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20should%20have%20taken%20a%20look%20at%20the%20MS%20support%20pages%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EIf%20the%20data%20set%20contains%20no%20duplicate%20data%20points%2C%20MODE.SNGL%20returns%20the%20%23N%2FA%20error%20value.%0A%0AIf%20the%20data%20set%20contains%20no%20duplicate%20data%20points%2C%20MODE.MULT%20returns%20the%20%23N%2FA%20error%20value.%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1531757%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20calculating%20modus%20is%20not%20always%20correct%20AGGREGATE(13%3B3%3BVALUES)%3B%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1531757%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20that%20information.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20the%20two%20functions%20are%20created%20to%20give%20the%20modus%20of%20numbers.%3C%2FP%3E%3CP%3EIf%20all%20the%20numbers%20appears%20once%20in%20the%20list%2C%20they%20are%20all%20the%20modus.%3C%2FP%3E%3CP%3ESo%20the%20fuctions%20aggregate%2C%26nbsp%3B%3CSPAN%3E%26nbsp%3BMODE.SNGL(values)%20%3B%26nbsp%3BMODE.MULT(values)%2C%20do%20not%20where%20they%20are%20designed%20for...%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhere%20can%20I%20find%20the%20MS%20support%20pages%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGreetings%3C%2FP%3E%3CP%3EErwin%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1531935%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20calculating%20modus%20is%20not%20always%20correct%20AGGREGATE(13%3B3%3BVALUES)%3B%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1531935%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F730715%22%20target%3D%22_blank%22%3E%40ErwinVanLoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHow%20to%20interpret%20no%20duplicates%2C%20return%20all%20numbers%20or%20handle%20no%20most%20frequent%20number%20situation%20depends%20on%20concrete%20project.%20From%20this%20point%20of%20view%20Excel%20correctly%20returns%20an%20error%20which%20we%20could%20interpret%20as%20necessary.%20That%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIFNA(TEXTJOIN(%22%2C%20%22%2C1%2CMODE.MULT(A2%3AF2))%2C%22no%20duplicates%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eor%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIFNA(TEXTJOIN(%22%2C%20%22%2C1%2CMODE.MULT(A2%3AF2))%2CTEXTJOIN(%22%2C%20%22%2C1%2CA2%3AF2))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eor%20other%20variants.%20Result%20looks%20like%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20127px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F206287iB8BFEA4CFB88A9A7%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1531892%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20calculating%20modus%20is%20not%20always%20correct%20AGGREGATE(13%3B3%3BVALUES)%3B%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1531892%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F730715%22%20target%3D%22_blank%22%3E%40ErwinVanLoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20send%20feedback%20via%20Help%20-%26gt%3B%20Feedback.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20the%20support%20pages%3A%3C%2FP%3E%3CP%3EIn%20the%20browser%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.microsoft.com%2F%3C%2FA%3E%26nbsp%3Band%20klick%20through%20the%20options.%3C%2FP%3E%3CP%3EIn%20Excel%3A%20F1%20or%20Help%20-%26gt%3B%20Help%20or%20click%20on%20the%20function%20name%20in%20the%20tooltip.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

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

 

ErwinVanLoo_1-1594909985105.png

 

 

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?

 

Greetings

E.Van Loo

 

 

 

10 Replies
Highlighted

@ErwinVanLoo 

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

image.png

 

Highlighted

@Sergei Baklan

 

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?

@ErwinVanLoo 

That could be

image.png

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

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.

 

Greetings

Erwin 

Highlighted
Highlighted

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

 

Greetings

Erwin

 

Highlighted

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

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 

Highlighted

@ErwinVanLoo 

You can send feedback via Help -> Feedback.

 

For the support pages:

In the browser: https://support.microsoft.com/ and klick through the options.

In Excel: F1 or Help -> Help or click on the function name in the tooltip.

 

Highlighted

@ErwinVanLoo 

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

image.png