MODE.SNGL works, AGGREGATE does not. Why?

[RatExcel]

Hello,

Can anyone tell me why this function works:

=MODE.SNGL({1,2,3,1,2,1})

and this one does not?

=AGGREGATE(13,4,{1,2,3,1,2,1})

I've tried to use [k] = 1 in AGGREGATE, I've tried CTRL+SHIFT+ENTER but still no luck.

Anyone?

 

[XOR LX]

Hi,

It's not just parameter 13.

For parameters 1-13 inclusive the array passed to AGGREGATE must be an actual worksheet range.

Only with parameters 14-19 can you use arrays which are array constants. This applies also to arrays resulting from some calculation performed on another array, e.g.:

=AGGREGATE(14,6,B1:B10/(A1:A10="A"),1)

which returns the largest value in B1:B10 whose corresponding entry in column A is "A".

Regards

 

[RatExcel]

Ok, I get it, but for me personally it does not make much sense. Microsoft could take a look at it. Or maybe this is programmed this way on purpose. Either way, thank you very much!

 

[XOR LX]

Ok, I get it, but for me personally it does not make much sense. Microsoft could take a look at it. Or maybe this is programmed this way on purpose. Either way, thank you very much!

You're welcome!

And I totally agree with you. Not only can I not see a great reason why the decision to give this functionality to only a small number of the available parameters was made, but it also seems to have come very poorly advertised, inasmuch as only a few people seem to know about precisely which parameters have this functionality.

Cheers