This is what I like! I was aware of the AGGREGATE function, but this is more than an excellent introduction. It's a great review and reminder of the features and gotchas of the function. Thanks!

@MyOnlineTrainingHub

7 ай бұрын

Thanks for your kind words 🙏😊

Excellent job Mynda. This is really the Swiss Knife of functions. This is amazing and thank you for bringing it up back to our attention.

@MyOnlineTrainingHub

7 ай бұрын

So glad you agree 🙏😊

That’s great Mynda.

@MyOnlineTrainingHub

7 ай бұрын

Thanks so much, Sergio! 😊

Mynda, you are so great! Thanks for this lesson!

@MyOnlineTrainingHub

7 ай бұрын

Thank you so much! 😃

the hidden gem of excel functions. i use it all of the time.

@MyOnlineTrainingHub

7 ай бұрын

Awesome to hear 👍

Excellent video as always. Thanks Mynda

@MyOnlineTrainingHub

7 ай бұрын

Glad you enjoyed it! Thanks for watching 🙏😊

Wonderful! Very powerful! Thanks a lot!

@MyOnlineTrainingHub

7 ай бұрын

Glad you liked it! 🙏😊

First rate. I was never clear on the k variable listed for aggregate -- now I am. Also like how you returned multiple answers with an array constant. Thanks!

@MyOnlineTrainingHub

7 ай бұрын

Awesome to hear 🙏😊

This is awesome Mynda. This is one of those functions I know is out there but rarely use, but you've shown me some great ways to use it, thanks!

@MyOnlineTrainingHub

7 ай бұрын

Glad you enjoyed it, Chris! Thanks for watching 😊

@kevinmahernz

7 ай бұрын

Same here, was aware of it but hadn't bothered to look deeply at it. Thank you.

Nice feature, well explained thanks Mynda!

@MyOnlineTrainingHub

7 ай бұрын

Thanks so much, Roger 🙏

Thank you so much. Need to spend some time with them for familiarise with the kids. Loved you image in a bubble too, thus you keep us company all along.

@MyOnlineTrainingHub

7 ай бұрын

Great to hear! 🙏😊

You are the best Excel guru out there. Love your videos 🙂

@MyOnlineTrainingHub

7 ай бұрын

Wow, thank you! 🥰

Outstanding! dear Mynda Treacy - You are number ONE! Very powerful function! Best regards from Limón, Costa Rica!

@MyOnlineTrainingHub

7 ай бұрын

🥰 thanks so much 🙏 glad you liked it!

Many thanks 🎀 🎀 🎀

@MyOnlineTrainingHub

7 ай бұрын

You are so welcome! Thanks for watching.

Excellent video!

@MyOnlineTrainingHub

7 ай бұрын

Thank you very much!

brilliant!

@MyOnlineTrainingHub

7 ай бұрын

Thank you!

Awesome!

@MyOnlineTrainingHub

7 ай бұрын

Glad you think so!

Oh wow, thank you!

@MyOnlineTrainingHub

7 ай бұрын

You're welcome! 😊

This is money! Fantastic explanation and example

@MyOnlineTrainingHub

7 ай бұрын

So pleased you enjoyed it 🙏😊

Very very useful I learned something here

@MyOnlineTrainingHub

7 ай бұрын

Awesome to hear! 😊

Verry good video , also verry to extremly usefull . it will safe me tons of time .

@MyOnlineTrainingHub

7 ай бұрын

Thanks so much. So pleased to hear that 😊

Hi Mynda, this is a fantastic video. I have just been reading through the email that supports this. It's great also to see good use (I am guessing) of the PowerPoint Cameo tool. 👍🏽👏🏽👏🏽👏🏽👏🏽

@MyOnlineTrainingHub

7 ай бұрын

So glad to hear 🙏 haven't used PowerPoint cameo tool though...Googling that now 😁

Cheers! 🥂

@MyOnlineTrainingHub

7 ай бұрын

Thanks for watching, Steven 😊🙏

Thank you for this video! ... 🤔 ... 😳 ... WOW!🤩🤩🤩

@MyOnlineTrainingHub

7 ай бұрын

😁thanks for watching!

Great

@MyOnlineTrainingHub

6 ай бұрын

Glad you liked it!

Once again 💅🔨 (nailed it) ❤😎

@MyOnlineTrainingHub

7 ай бұрын

🥰🙏

thank you very much! how could I not know about this?!)😀

@MyOnlineTrainingHub

7 ай бұрын

Better late than never 😁

Hi Mynda, can you also use what you have described here in a Pivot table? I don't know how to reference the pivot table column name in my formula.

@MyOnlineTrainingHub

7 ай бұрын

To reference a PivotTable, you should use the GETPIVOTDATA function explained here: kzread.info/dash/bejne/fniawbGLnrappZc.html

Mynda, thank you once more for this very useful video! Could you please post the link to the video you mentionned on how to use the aggregate function with slicers and tables?

@MyOnlineTrainingHub

7 ай бұрын

oops, this video was supposed to be for next week 🤦‍♀️ here's the other video: kzread.info/dash/bejne/nGmcs5WQcduco6w.html

@olivierissaverdens6916

7 ай бұрын

@@MyOnlineTrainingHub Thank you very much Mynda!

Great video Mynda. I have two questions. 1) does Aggregate exist in version 2016? And 2) can we include formulas in our VBA code? (Or at least reference them...).

@MyOnlineTrainingHub

7 ай бұрын

Thank you! Yes, it's available in all versions from 2010 onward. AGGREGATE works just like any other function in regards to VBA.

Hi Mynda, I learn a lot with each of your videos! Many thanks! I have one question regarding the "calculation" for the result in 4:32: The results from the >average function (without errors) are divided by the array of the viewers. How is this working in the background? I don't get it. Not sure if my question is clear. So I'll try this way, too to hopefully can transfer what I mean: If I divide an Array through a subset of the same array: What is there calculated in the background? Many thanks for any help. 🙂

@MyOnlineTrainingHub

7 ай бұрын

Each of the 12 values in the Viewers column is divided by the corresponding value returned by the denominator array. e.g. 91/0, 87/0, 99/0, 102/0, 125/0, 140/1....

@MMSummer26

7 ай бұрын

@@MyOnlineTrainingHub Ah, all right. Now I know where I made my mistake. The logical test does not return the value for a match, but the 1, which can then be evaluated using the aggregate function. Many thanks!!! 👍♥

Wow! at 5:20 So AGGREGATE does not ignore manually hidden rows, but SUBTOTAL will. That is good to know! I had wondered if SUBTOTAL had been rendered useless, but there is at least one use-case in its favor. Anybody aware of any other SUBTOTAL use cases that AGGREGATE doesn't do?

@MyOnlineTrainingHub

7 ай бұрын

Glad it was worth your time to watch 😊🙏 AGGREGATE does ignore both filtered and manually hidden rows. SUBTOTAL has an option to not ignore manually hidden rows, but only if not used with filtered rows at the same time!

@TC_99

7 ай бұрын

Subtotal ignores other Subtotal functions in the range.

Very Nice, Mynda! Thanks! If you get a Pivot Table with Aggregate Value and Aggregate Value2 in Column Bucket of Pivot Table Field List. How should you check the Details of Aggregate function in those Fields? Thanks

@MyOnlineTrainingHub

3 ай бұрын

Thanks! The PivotTable can't tell you the underlying formula in the source data for any column and AGGREGATE used here is no different. You'd be best to avoid making the formulas for PivotTable source data changeable for this reason.

@shoppersdream

3 ай бұрын

@@MyOnlineTrainingHub Thanks! The problem is that there are already a lot of formulas in the Data Source. Maybe they were used as Helper Columns. Thanks

Awesome functions. My only worry is how to get to this video when I need to use those functions. Can I tag this video in KZread with specific tags so, when I search with those tags, I get to this video?

@MyOnlineTrainingHub

Ай бұрын

You can bookmark it and save it to a playlist for future reference.

This was a video of a subject “on steroids”. Well done and thank you!

@MyOnlineTrainingHub

7 ай бұрын

Thanks so much, Geert!

Is this function available in excel 07?

@MyOnlineTrainingHub

7 ай бұрын

07!!! Time to update, I think 😉 No, it's only available in Excel 2010 onward.

ln luganda we say, webale nyo! To mean Thank You

@MyOnlineTrainingHub

7 ай бұрын

My pleasure 😊🙏

❤

@MyOnlineTrainingHub

7 ай бұрын

First! Thanks for watching.

Nice pill.

I am not a professional user of excel rather i use excel as a fun. i have an issue though irrelevant to the topic but i think you can do this. How can we handle if a product of two digits cross the limit of 15 digits.

@MyOnlineTrainingHub

7 ай бұрын

Happy to help, but please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

@afzaalahmed9565

7 ай бұрын

@@MyOnlineTrainingHub ok thanks, I will upload my excel file and question and wait for solution.

♥

@MyOnlineTrainingHub

7 ай бұрын

🙏thanks for watching!

Damn you look even better now than 15 years ago. You age like wine!

@ericgora2402

7 ай бұрын

Must be because she's saving her self the stress of misusing Excel for over 15 years

@MyOnlineTrainingHub

7 ай бұрын

😁 hidden benefits.

Some aggregate functions like arrays don't work if your admin has limited user's "connected experiences". Excel coughs up this error box: "This command isn't available. Your organization's administrator turned off the service required to use this feature." and your cell has the "#NUM!" error

@MyOnlineTrainingHub

3 ай бұрын

That wouldn't apply to this function. That'd be for functions like STOCKHISTORY that reach out to external sources for the data.

@funloving2785

3 ай бұрын

@@MyOnlineTrainingHub Well, since I've experienced it directly I know this to be accurate. I'd be happy to show you a screen shot of the error, as well as Microsoft's explanation of what check box to click to correct it.

@MyOnlineTrainingHub

3 ай бұрын

Yes, please. I can follow up with Microsoft through my MVP channels. You can email me: website at MyOnlineTrainingHub.com

That’s all good, but this functionality is a couple decades too late. Might as well learn Python/Pandas and free yourself from Excel’s limitations. Having said that, if Excel is a must, then this is very useful.

@MyOnlineTrainingHub

7 ай бұрын

Thanks for watching!

Linda, this is the first time I've seen your erroneous conclusion! The Agregat function ignores any hidden lines - whether it's done by a filter or manually, you're wrong!

@MyOnlineTrainingHub

7 ай бұрын

I disagree. If you use any of the options numbers that ignore hidden rows (i.e. 1,3,5,7), AGGREGATE ignores rows hidden using the filters OR manually hiding them with right-click. Whereas SUBTOTAL will not ignore rows hidden using right-click > Hide Row when using function numbers 1 through 11.

@6lskill103

7 ай бұрын

Alas, your opinion does not change the essence. Everything is exactly the opposite))) I'll be sorry if this offended you. With great respect for your work and experience!

Mynda, you are pretty.

@kylelane630

7 ай бұрын

Talented in her way of sharing knowledge and skills is what I say

@MyOnlineTrainingHub

7 ай бұрын

Thank you!

Awesome!

@MyOnlineTrainingHub

7 ай бұрын

Glad you liked it 🙏