MODE.SNGL works, AGGREGATE does not. Why?

RatExcel

Board Regular
Joined
Aug 24, 2014
Messages
222
Hello,

Can anyone tell me why this function works:

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

and this one does not?

Code:
=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?
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
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
 
Last edited:
Upvote 0
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! ;)
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top