Excel Array Formulas & Functions: 50 Examples of How to Become an Array Expert! - 365 MECS 09

[@skimpylemon8034]

I'm genuinely baffled by why this content is FREE. You blow my mind with every new video! Thank you Sir! :)

[@excelisfun]

There are people in the world that want to make art and have people view it for free ; ) Since my duty as a human is to try and make the world a better place, and Excel is so much fun, I get to have fun WHILE trying to make the world a better place!!! You can also think of me as someone who wants to subvert the system by using social media to spread good, rather than using social media to spread bad. However you want to view it: great to have you part of the Team!!!

[@skimpylemon8034]

@@excelisfun WOAHHHH, dropping gems of pure sincerity! I love your mentality as it resonates with my worldview. I don't particularly like social media due to all the negativity being metastasized but your content is a source of good. I hope to cross paths with you one day!

[@excelisfun]

@@skimpylemon8034 The good news is: we are crossing paths now, with more to come!!!!! Here is more of my world view, my 60th birthday party (the video I posted right before this one): kzbin.info/www/bejne/f6nIaquhZbqMqtk

[@skimpylemon8034]

@@excelisfun Ahahahha, smashing it! Its people like yourself that make the world a better place and make young people like me to achieve more than I can imagine. Thank you again!

[@excelisfun]

@@skimpylemon8034 Yes!!!! Young people like you are the reason that the future is bright and radness and world views like yours will continue to subvert in a positive direction!!!! Hey, were do you live and how old are you? I am 60 and live in Seattle, WA, USA.

[@TheLittleRyeAlchemistintheLigh]

This might sound so cheesy and a bit too much. But dare I say that this world needs more people like you. So generous. So smart. Thank you. You’re a lifesaver.

[@thryce82]

this channel has helped me out so much at work. I hate excel like reallllllly hate excel because its so buggy on meaningful amounts of data. But this channel has taught me enough that when excel actually works it can be a joy. thanks for that

[@smilewidedontbeshy]

Priceless!

[@excelisfun]

Glad the free fun helps!!!!

[@JoseAntonioMorato]

Dear Mike, Your «Budget and investment examples», gets more amazing if reduced to a single cell: =LET(Year,SEQUENCE(C27,,C26,C28), Rate,SEQUENCE(,C30,C29,C31), HSTACK(VSTACK("Year/Rate of Return",Year),VSTACK(Rate,FV(Rate,Year,-C32,-C33)))) 🤗

[@excelisfun]

Thank you for that single cell reprot, Jose!! I have added it to the Excel file download workbook so the rest of the Team can see!!!!! Go Team!!!!!

[@efficiency365]

Outstanding. A lifetime of Excel in one hour.

[@excelisfun]

Glad you liked this!!!!!!

[@StopWhining491]

Great explanation, especially establishing the boundaries about what an array is not. Too many instructors leave out that part in their videos. Your notice that in an array the formula is only in one cell was key.

[@excelisfun]

You are welcome!~ And you are so right, many teachers don't get array formulas and so they can't teach it. There is a book that was just published about array formulas and it is terrible. No parameters at all and incorrect information about array formulas. I will solve that soon. I have an array formula book coming out in about 6 months : )

[@azizurrehman6296]

Sir i am really your big Fan. From Pakistan.....i see content rarely like this...in free for the public... ....your every tutorial give me a lot of concept and it give strong base to understand everything from basic to advance i solve many problems in Excel after watching your videos...alot of confident...i search your every video like Excel beginner, then power query completer course and now this brand new course ..it's really a complete story...Excel 365...but you explain very well ..i don't have words for this ..

[@excelisfun]

I am so happy that you like what I post. This is what I have been doing for 15 years at KZbin: free education for the world!!!!

[@bbotzong]

Nicely done!

[@petardzopalic3220]

Thank you Mike for your teachings, much have I learned from you since 2013.I am from 3 world countries and thank you because you teach for free.

[@excelisfun]

You are welcome! That has been my goal for the past 15 years at KZbin : ) So Happy to help!

[@RajaNatarajan-g3t]

Mike sir, you are mother/father of all teachers. Thanks to all your efforts and time for creating such content for free. If i am given a chance for Noble prize in the field of teaching and education, you will be top of my list 😀👍👍👍

[@excelisfun]

Thank you for your kind words : ) : ) : )

[@LostAccount-us5fe]

Really really glad I asked chatgpt to recommend best youtube channels for Excel. Thank you for such great quality content. ❤

[@excelisfun]

You got it!!!! As you may or may not know, ChatGPT is very unreliable for actual Excel and Power Query and Data Model formulas. The answers it gives can miss very basic things. But man, I had no idea that it would suggest my channel. My channel is the only channel with full high quality (hows and why of what you are doing) Excel content that is free : ) So glad you made it here!! For 15 years I have posted over 2,500 vids and many free classes. If you can't find somrthing, just ask, although the auto play vid on my KZbin home page shows how to use the channel : )

[@vosk875]

Mike, you are truly a prophet, an oracle of sorts in our lifetime. It's so true, Excel is grossly underrated and underutilized in the world of education and business workflow. For some reason, PowerPoint and Word usually take precedence. It's difficult to comprehend this conundrum and the fear that many people have of spreadsheets and data analysis. It's significantly overlooked in high schools and universities and many corporate environments. I work in the area of real estate finance and have been helping people with financial modeling/forecasting/valuation (students at all levels as well as companies seeking training) on a part time basis. Since taking a deeper dive into your invaluable materials, especially array operations and sophisticated lookup situations, demand for my services have notably risen. There is high demand out there and the rewards are immeasurable. Thank you so much for your service.

[@gayaa6490]

Thanks so much, Mike. I hope you've been well :)

[@excelisfun]

You are welcome so much! Things have been well : )

[@lucaviglio1206]

Finally i managed to study this another amazing tutorial.....i'm amazed about how versatile these new formulas are...Thank again for your work Mike

[@richardhay645]

Great demonstration of your mastery of array functions!! I saw it posted Saturday night but no time to watch till this (Monday) morning! BTW I recently helped someone with UNIQUE and she eded up with a distinct list of about 1100 items. She needed to move this result down a couple of rows. She started to highlight the entire long list. She was greatly relived that she only had to click and drag the one cell that contained the formula and all the remaining items followed along like obedient puppies!!!

[@excelisfun]

That is a perfect example of how dynamic spilled arrays just seem to make everything easier : ) : )

[@vijaysahal4556]

Super cool 👍 Mr Mike

[@excelisfun]

Yes!!!! Glad it is cool, Vijay!!!

[@miv0077]

Thank you so much for sharing your expertise and for your time teaching people completely free! Truly grateful. God bless you.

[@excelisfun]

You are welcome for the free Excel Fun!!!!!

[@reanalytics1863]

You are the Excel Genius, I never get tired of your lectures. I recently started learning Python. I have the impression the new excel functions make excel almost as powerful as pandas in manipulating data. There is so much possibility unlocked with those formulas. SO SO AMAZING to see the crazy stuff you do with those array formulas.

[@excelisfun]

I am glad to share the crazy array fun with you!!!!

[@andrewjohnson4352]

Great video! Thx!

[@excelisfun]

You are welcome :) !!!

[@isaacsewornudzordzorme3301]

ExcelIsFun Indded. Thank you Mike. Have been following you for at least 7 years

[@excelisfun]

Thank you for your support for SQRT(49) years!!!!!

[@canirmalchoudhary8173]

Great detailed video on dynamic array and array functions thank you 😊

[@excelisfun]

You are welcome!!!

[@realtoast7036]

You wouldn't believe how much these lessons comport with my actual sales manager workday! I can't watch for free

[@excelisfun]

I love to hear that it comports with what you do. For over two decades, I am continually amazed at how few people use Excel to make their lives easier. I am so glad you are one of the few who takes the time to study and learn cool techniques to make things easier and so much more fun!!!

[@excelisfun]

Thank you so much for the kind donation!!!!!

[@saurabhbhardwaj9667]

Dynamic arrays are so much fun. Thanks for posting another epic video in 365 MECS series. Your playlist on Excel Dynamic arrays which comprises of about 130+ videos is powerhouse of immense knowledge and filled with so much fun. I loved it a lot and gained so much from it. I simply wish that Microsoft introduces all the great dynamic array functions like Vstack, Hstack, lambda, torows, tocols, chooserows, choosecols etc in all the 365 versions and not just in insider edition.

[@excelisfun]

They should be out very soon. I actually thought that they were already out...

[@saurabhbhardwaj9667]

@@excelisfun Microsoft has still not introduced these functions in all 365 versions. I have version 2202 in my machine and it still doesn't incorporates all these exciting functions. For Lambda, how beautiful it would be if Microsoft were to make a provision to add the user defined functions in a sort of some library where user can pull off those functions for use anytime and their use is not just restricted to a particular workbook that they were created in. A differentiator of "global library" vs "local library" could be used by the user to either allow that function to be made available to a workbook where it was created (local library) or any other workbook that user creates (global library). In my opinion, this would be revolutionary.

[@nsanch0181]

I'm not done with the video, it's taking me a couple mornings to study it, but this is FANTASTIC! I just wanted to share that with you Mike. I'm most excited about the Re-Orient Data Functions like TOCOL and TAKE. Cool stuff Mike thank you so much.

[@excelisfun]

You are welcome so much!!!! Studying a very detailed video like this and using it as a reference later will help you to became an even better Excel Master than you already are, nsanch0181!!!!!

[@visakhsarma9089]

Found this channel from reddit. You are amazing.

[@excelisfun]

Glad I am here to help! Been here for 14 years, over 3,500 videos. Were you looking for array formula stuff, like in this video?

[@visakhsarma9089]

@@excelisfun excel in depth. Tbh, I feel guilty watching this for free. Would love to donate when I land on a job. God bless you.

[@excelisfun]

@@visakhsarma9089 If you want all the detail and depth and aren't afraid of the whys that go behind the hows, you came to the right Excel KZbin Channel. Funny things is, most humans don't care about why, they just want how. But we humans that want the whys, like you, are the ones who have the power to do anything!!! Yah, watch and learn. If you want to donate later when you have $, $ Thanks button below each video or paypal donation link on KZbin Home Page.

[@excelisfun]

Also, thumbs up on each vid is a good payment ; )

[@SyedMuzammilMahasanShahi]

Thank you Mike for this EXCELlent video.

[@excelisfun]

You are welcome, Fellow Teacher : ) : ) : ) : )

[@excelisfun]

I have added 10 practice problems (homework problems) to the end of the download workbook - so that you can test your skills : ) Also: at 04:55 the label should read "Total Commissions", not "Total Sales" lol

[@henryg5735]

Brilliant as usual Mike - I sit at my computer in awe! And it's not only the subject content that is so valuable but also the "little gems", where I have forgotten or not come across a tip eg resizing a table by dragging on its end handle. 👏👏👏

[@excelisfun]

Yes: so many tricks, and endless array of new tricks to bump into every day : )

[@nishantkumar9570]

All your videos are epic! You and your way are awesome! Thank you so much Mike.

[@excelisfun]

You are welcome!!!!!

[@Softwaretrain]

Incredible deep dive into array formulas. Thanks Mike. We can see with these new functions almost everything is possible even easier than before. Thanks for sharing. Waiting for the next video (Lambda and its helper functions). Hope to see a lot of ETL examples with a lambda function.

[@excelisfun]

The next video is the video I have been waiting to make for almost a year : ) : ) : ) : ) However, I do not have any ETL examples planned. When I think ETL, I do not think LAMBDA, I think Power Query. I have some good examples to help illustrate the power of LET and LAMBDA, but what ETL examples did you have in mind? ETL almost always has to do with connecting to external data? I am not sure how LAMBDA can do that... ??? What is on your mind, Smart Teammate Softwaretrain, about ETL and LAMBDA? Now I am curious : ) : )

[@lourdesdelcampo8722]

Thank you for the video, I am working my way through the book and the videos are a great help, great content as always

[@excelisfun]

The videos have some of the more recent functions that Microsoft added just as i was publishing. You are welcome for the videos! Thanks for buying the book!! If you bought at Amazon, please leave a review : ) : ) : ) : )

[@lourdesdelcampo8722]

@@excelisfun will do! the book is excellent and so far thanks to it I have shortened the time it takes for me to do some look ups that just did not have to be as long or complicated as I thought, thanks again

[@excelisfun]

@@lourdesdelcampo8722 I love to hear that: making better lookup formulas: ) Thanks for the review - it helps.

[@grzegorz2852]

Man, you're the best. Great explanation, not boring at all, looking forward to more :)

[@excelisfun]

Glad to help with the un-boring : )

[@nadermounir8228]

A super insightful Video Mike 📹 👏. One little comment: I use the word Total at the end of my table without Vstack since we don't have insider edition at our organisation. The way I do it is by using Sortby function placing the total at the top of the table along with the sum of the values. I also use the sequence function to create a list of numbers from 1 till the end of the table and above the first row I do rows(A3#)+1. I them use the sort by function as follow: Sortby(B3#:B2,A3#:A2, 1)

[@darrylmorgan]

Boom!Super Fun Class To Start A Sunday Morning...Thank You Mike :)

[@excelisfun]

Yes!!!! Sunday Morning Excel Array Formula Fun!!!!

[@davidabuang]

Great summary video of the new array functions, especially the obscure ones that seemed pointless at first glance. Nice to see some realistic uses for them. Thanks!

[@excelisfun]

You are welcome!!!

[@enayetrashid9566]

Mike, I have a request. Can you make a video regarding "day in life of a excel MVP". Thank you for your hard work and effort.

[@excelisfun]

That would be a boring video, just 11 hours in a room filming and editing... That is what I did today : ) lol My day is just like yours ; ) We both have fun with Excel!!!

[@ismaelkourouma5558]

Awesome video as usual. Thanks Mike

[@excelisfun]

You are welcome, ismael!!!!

[@zt.5677]

Do I calculate it correctly that from 2008 to 2023 makes ExcelsFun exactly 15 years old this year? That is something to mark: that is 15 years of excellent teaching, enthusiasm and great business Excel videos. Thank you, Mike.

[@excelisfun]

I think my 15th b-day at KZbin is Feb 8 this year, in about 1 month. I will make a video to celebrate then...

[@zt.5677]

@@excelisfun I am looking forward to it. Happy anniversary!

[@excelisfun]

@@zt.5677 At 60 years old, I finally made it to 15 lol

[@bucs2021]

Exceptional, as always. Never stop giving; Wish you the best! I see this series(365 MECS), as video tutorial version of your late book: The only app that matter...Love it!

[@excelisfun]

Thanks for the love and glad you like the vid, metalurgy1988!!!

[@chrism9037]

This was a great "array" of epic EXCEL-lent examples, thanks Mike!

[@excelisfun]

You are welcome, Long Time Teammate Chris M!!!!!

[@darrenlee6462]

Fantastic Video Mike. Materials were very helpful for working along. Really appreciate you making this!

[@excelisfun]

I am glad that all the things I post help, Darren!!!!

[@GeertDelmulle]

Mike, super great video about my most favorite topic: dynamic arrays! Yay! 😀

[@GeertDelmulle]

In your initial excitement you made a little mistake: @4:55 you should have calculated the total sales (as per the title), not the total commission. ;-)

[@GeertDelmulle]

PS: @9:14 Function Argument Array Operation: that’s what I call ‘vectorized’ for short (a function argument that is expected to be scalar-valued is replaced by a (numerical) vector - which in general could be an entire array). MS calls this ‘lifting’. When 2 scalar arguments get vectorized (‘lifted’), things get even more interesting: in that case the 2 vectors could be parallel, or perpendicular. The first case I call ‘parallel vectorization’ (MS calls this ‘pairwise lifting’) and the second case I call perpendicular vectorization (MS calls that ‘broadcasting’). I find my terminology more appealing and more intuitive.

[@GeertDelmulle]

Mike, here’s a suggestion: @18:00 on the conditional formatting: next time try the second option “Format cell that contains”, and then choose the option ‘cell is not blank’ for the condition. Same result. (No need to write an explicit condition formula.)

[@GeertDelmulle]

50:30 Not so long ago in response to a Mr.Excel challenge I used EXPAND in a formula to insert a blank row every 2 rows in a table.

[@GeertDelmulle]

As a final note: these MECS videos are DaBomb!! They are so feature packed, it’s “ridiculous”. At first I was a bit weary watching this series because these videos are so long, but it has become a most liked format of mine: thematically group things together for the complete package. WOW, just WOW! These are BIG presents to the team and the entire community - not just for Christmas but all year round! Your altruism is legendary, going on devine! Thank you so much! 🙂🙂🙂

[@lucaviglio1206]

Amazing Mike...still not have the new 365 new formula but this Is not an excuse for not studing...thanks a lot for this wonderful tutorial

[@excelisfun]

You are a TRUE Excel Pro and Enthusiast because you are still studying even though you don't have them. That is good : ) Microsoft should release them soon...

[@lucaviglio1206]

@@excelisfun Thank you so much🙌🙌

[@kiwikiow]

It's a great video. I have fun and learn new Excel tips. Thank you very much Mike 💚👍👍

[@excelisfun]

I thought of you when I put the ghost in : ) : ) : )

[@kiwikiow]

@@excelisfun 😁❤️👻

[@ohdjrp4]

Thank you very much ExcelIsFun!

[@wayneedmondson1065]

Epic and awesome, as always! Thanks Mike :)) Thumbs up!!

[@excelisfun]

Thanks, Wayne!!!!!!! : ) : ) : )

[@RogerStocker]

Really the complete story, thank U so much, Mike! You're just great.

[@excelisfun]

You are welcome for the complete story!!! This one did take a LONG time to make...

[@மின்விரிதாள்_விரிப்போம்_வாங்க]

Wow. Great collection Mike.

[@excelisfun]

I am so glad that this helps!!!!!

[@waitplanwp4129]

always a pleasure, love u mike!

[@excelisfun]

Thanks for the love, WaitPlan!!!!

[@markpodesta4605]

Thank you Mike.

[@excelisfun]

You are welcome, Mark!!!!

[@0909cxc]

What wonderful presentation. Thank you, sir!

[@mohamedchakroun4973]

Excel is fun The best way to learn Array :-)

[@excelisfun]

I love your poem : ) : ) : )

[@wmfexcel]

Hi Mike, thank you so much for this video. This is amazing! Your video and the new array functions!

[@excelisfun]

You are welcome, wmfexcel!!!!!

[@sjn7220]

Wow! So informative and useful. Thanks so much for this.

[@excelisfun]

You are so welcome for this!!!!

[@krn14242]

Wow Mike, what a great inclusive video.

[@excelisfun]

Wow!!!! The original World Record Holder here at excelisfun!!!! So wonderful to see you : ) : ) This is a good one to watch, even if you are really good with Excel, because I tried to put all I know about the new array formulas in one video : )

[@abeerattia4523]

Thank you for this excellent video , keep on that

[@excelisfun]

Glad you like it!!!!

[@abderrahmanebabaazizi6458]

Thanks MIKE for the content of the Great video;I have encountered a small problem The comma does not work;={1,2} displays 1,2 .How to solve This problem for display array in Row

[@excelisfun]

I have NO idea. I have NEVER seen that before, and I can not simulate it. I just tried a number of things, but I still have no idea... Maybe you have your sheet in Audit Mode?

[@yashrsingh]

Thank you for these amazing videos

[@excelisfun]

You are welcome for the amazing!!!!!

[@AnandGautam9901]

Love Arrays

[@excelisfun]

100% agree I love arrays because they make most things easier : ) : )

[@AkhilAkkarleni]

Hello Mike! Great content as always! But for some reason I'm not able to use most of the functions like SORT, UNIQUE, TOCOL, TOROW, WRAPCOLS, etc. Can you please help me with this? My Excel version is 2016 Home and Student Version.

[@johnborg5419]

Thanks Mike. That was a great video!!!! :) :)

[@excelisfun]

You are welcome, Formula Guy!!!!!!

[@heshamfouad8886]

Hello, Thanks for the Fantastic Video as usual, I always watch Your videos even When I already know the topic by heart, as I always learn 2,3 (in this video 5) hidden tricks If I may ask You, in the excel file in the Expand part, You a solution from one the comment for a guy named Victor, Can You maybe explain, why does the IFNA works ? I can't get my head around it

[@chrisp-of1jt]

I have an array formula trying to build that needs a total on the side and bottom. Seem to be unable to make work using drop and then summing totals in the columns as we did in row. Similar to problem "Create Dynamic Product sales with Total Rows" want to by rows and columns. Any videos I can watch to see how that is done? Or any suggestion? The column is exactly same as rows. Your videos are amazing!!!! Just an old guy learning to do excel.

[@thomasarandt5677]

Awesome videos!

[@excelisfun]

I am glad that you enjoy the videos!!!! What were some of the things that you learned that were most intersting?

[@alvegrande7398]

damnn im excited for next videosss

[@excelisfun]

Me too!!!! I can't wait : ) I have had bad health the last year and so my pace of releasing videos has slowed down. I hope I can get the video out in the next month. It will be a good one : ) : ) : )

[@alvegrande7398]

@@excelisfun thank u so much mike! I wish you and your family good health

[@Excelambda]

Great Epic Video !!✌Follows the tradition of Ctrl+Shift+Enter series, the GOAT YT videos about Excel.

[@excelisfun]

Thank you, mots awesome Teammate : ) : ) : )

[@excelisfun]

Happy holidays in Spain, my dear friend Excel Lambda : ) : ) Well. I have wasted 2.5 hours on Thanksgiving morning trying to spill a formula that will enact a running sub-count for consecutive numbers. I have a none spill formula like this: G10 contains: 2 and I use this formula in Column H starting in cell H10: =IF(G10=G9,H9+1,1) to get: 1 G11 contains: 1 and I use this formula in Column H starting in cell H10: =IF(G11=G10,H10+1,1) to get: 1 G12 contains: 3 and I use this formula in Column H starting in cell H10: =IF(G12=G11,H11+1,1) to get: 1 G13 contains: 1 and I use this formula in Column H starting in cell H10: =IF(G13=G12,H12+1,1) to get: 1 G14 contains: 3 and I use this formula in Column H starting in cell H10: =IF(G14=G13,H13+1,1) to get: 1 G15 contains: 3 and I use this formula in Column H starting in cell H10: =IF(G15=G14,H14+1,1) to get: 2 G16 contains: 2 and I use this formula in Column H starting in cell H10: =IF(G16=G15,H15+1,1) to get: 1 G17 contains: 2 and I use this formula in Column H starting in cell H10: =IF(G17=G16,H16+1,1) to get: 2 I have tried some crazy formulas with MAP, SCAN, OFFSET, DROP, CHOOSECOLS, IF and a bunch of others. I can't believe that I could not figure it out with 2.5 hours effort... : ( The good news is, I know that you can get this is a minute or two : ) Got a minute or two to create a solution for me? : )

[@Excelambda]

@@excelisfun Happy Thanksgiving !! =LET(a,G10:G17,s,SCAN(1,SEQUENCE(ROWS(a)-1,,2),LAMBDA(v,i,IF(INDEX(a,i-1)=INDEX(a,i),v+1,1))),VSTACK(1,s)) (first draft, if I will find a better one I will post it)

[@excelisfun]

@@Excelambda I have zero idea how that works. It is well above my array of Excel knowledge. I have no such concept in my Excel toolkit. But I will study it to try and add it to my Excel toolkit. Thank you VERY much for being soooooo smart, Excel Lambda : ) : ) : ) : ) I am lucky to be on a team with you: it makes me happy : ) I will study and report back...

[@Excelambda]

@@excelisfun A better one 😊 =LET(a,G10:G17,SCAN(1,SEQUENCE(ROWS(a)),LAMBDA(v,i,IF(i=1,v,IF(INDEX(a,i-1)=INDEX(a,i),v+1,1)))))

[@barttitulaerexcelbart9400]

This really is an epic video again, thanks! looking forward to your LAMBDA video, actually I am looking forward to all your video's 😉

[@excelisfun]

Thanks, long time Teammate Bart!!!! I got your e-mail, but I have been working 12 hour days while being really sick... I will get to it sometime.

[@skywalkerguy]

Hey Mike, That was an awesome video again. Could you please explain how did you put the cell reference along with formula used when you were using the "Formulatext " formula. E.g F27=sum(A1:A10) I hope I am able to express it clearly

[@excelisfun]

If formula in cell M22, this is one way to accomplish it: =ADDRESS(ROW(M22),COLUMN(M22),4)&": "&FORMULATEXT(M22)

[@excelisfun]

I actually will show this formula in the upcoming video about LAMBDA.

[@rehanshah2091]

If we use data validation list in any cell and want to open list dialogue without using mouse, we can just press alt + down arrow...came to know by mistake when I pressed wrong key combinations..

[@COURSSTATSCHAMBERY]

you don't need data validation. This short cut gives you th list of the item "over" your cell

[@excelisfun]

Thanks for the hot tip!!!!

[@excelisfun]

@@COURSSTATSCHAMBERY Thanks for the hotter tip ; )

[@AbdulAhmed-jb9he]

Hi, great video. When can we expect to see the next video uploaded in the MECS series?

[@excelisfun]

I have had very bad health, so it is delayed. I have already started it, but I am delayed. I am bummed. But in a week or two. It will be an epic video about the new frontier of Excel: LAMBDA : )

[@AbdulAhmed-jb9he]

Oh alright understood, I hope you get better man.

[@wiewiorek89]

On top of Excel NumPy and Pandas are also worth to know.

[@excelisfun]

I hope you liked the video

[@geewee1geewee197]

I have an excel question for you: I have some values in columns A, B and C. I need to sum values in column A if those values are between certain values in columns B and C. Say for example if B1

[@excelisfun]

I am sorry, but I do not understand your question, For back and forth dialog at the best Excel question site that I know, try: mrexcel.com/board

[@nsanch0181]

Thanks!

[@excelisfun]

Thank you for your kind donation, nsanch0181!!!! : ) : )

[@ronaldoliver1700]

Thank you

[@excelisfun]

You are welcome!!!!!

[@carlosmantilla7997]

Gracias.

[@excelisfun]

: ) : ) : ) : ) : )

[@carlo_migliari81]

Concerning the example nr. 12 i wanted a way to add titles to the columns; My idea is adding such description inside VSTACK before UNIQUE for both columns; the only difference stands inside DROP: you've to use UNIQUE one more time and put 0 instead of - 1. Let's suppose the starting table is called "tabb" and i start My formula in cell Q5 : =VSTACK("Product" ;UNIQUE(tabb[Product]) ;"Total") In Cell R5: =VSTACK("Sales" ;SUMIFS(tabb[Sales] ;tabb[Product] ;DROP(UNIQUE(tabb[Product]) ;0));SUM(tabb[Sales])) N.b.: you've to change the ";" with "," 'cause i'm using italian Version of excel 365

[@zhiqizhang4348]

Hello, I have a question . Tab 1671, what is the function of index? After UNIQUE(SORTBY()), we have two views, one is RANGE VIEW, one is array view (after the Unique function). We want to use SUMIFS formula, but F3# (I call it a Dynamic array view) does not apply. So we use index formula to break the array view( after unique rows function), to make it a range? Thanks

[@msantosh1220]

Thanks

[@excelisfun]

Thank you, thank you, thank you for the $ to help me make videos for the Team!!!!!!!

[@satyamsharma4663]

Love it...

[@excelisfun]

Yes!!!! I love arrays too : ) : )

[@krimbos1]

Can I use these concepts to create a dynamic price catalog? I have an extremely large spreadsheet with special pricing for many customers and products. The prices change each month due to surcharges and price changes. How can I create a dashboard to search by customer and display specific products and pricing?

[@zhiqizhang4348]

Hello, Dear Sir, I have a question. After converting a data set with formulas to a table, will the formulas function the same after the convertion ? THANKS POLITELY :)

[@excelisfun]

I am sorry I do not understand your question. Can you provide more details?

[@zhiqizhang4348]

@@excelisfun， since excel TABLE function has its own formula syntax or notations which I am not familiar with, may I complete all formulas writing, then convert the excel sheet to a table? I mean after the convertion, are the formulas still alive? I did some testings, but not sure about my conclusion. Thanks

[@excelisfun]

@@zhiqizhang4348 Mostly they are still alive. Occasionally when I have made formula prior to converting table to an Excel Table, I have had issues. I would just learn the Table Formula convention. It is not so hard. There are also other advantages to Excel Table Formulas, like the easy with which you can highlight a whole column or whole table. Here is a video for you that teaches Table Formulas: kzbin.info/www/bejne/rX3XeqN4p72Na68

[@zhiqizhang4348]

@@excelisfun THANK YOU ! I will remember it ...

[@excelisfun]

@@zhiqizhang4348 : ) : )

[@israel7058]

Will this formula apply in office 2019

[@TocaDefne4]

How can we use choosecols functions with table names. Choosecols(DATA;DATA[COLUMNA];DATA[COLUMNB]) instead of choosecols(A1:B200;1;2)

[@evilangel4136]

In sheet Fun, cell G4 should be total commission, not total sales..

[@excelisfun]

Thank you - I added a pinned note at the top.

[@viktorasgolubevas2386]

Another burst of expertise from Mike. A huge thank you! Some insights into the Re-Orient (Unpivot) solution. After some exercises and solutions with INDEX and SEQUENCE (fairly successful, but unhandy), I came across this one. Thanks to IFNA's Lucky Behavior, kind of UB :) =HSTACK( TOCOL(IFNA( BF3:BF5 , BG2:BH2 )), TOCOL(IFNA( BG2:BH2 , BF3:BF5 )), TOCOL( BG3:BH5 ) ) Still, academically, feeling uncomfortable using N/As and lego-like manipulations :))

[@excelisfun]

Amazing, victor!!!! It is so bizarre, though. But so much shorter than other possible solutions. Where did you bump into it? Just trying things? Or did you see it in a form? What is the lucky behavior that you are referring to? And what does "UB" mean? This will work also: =IFERROR(BF3:BF5,BG2:BH2) I am still trying to understand how it works. It seems that the first argument sees no errors, so because the two arrays (row headers and column headers) form a rectangle, it just takes items from first array and fills in the rectangle. It really seems like an ingenious formula. I must do a video about this? I hope you will answer my questions, so I can fully undertsnad. Thanks, victor!!!

[@viktorasgolubevas2386]

@@excelisfun UB - undefined behavior, just term in "dangerous" programming languages like C/C++ Mike, this is really example of KISS - keep it simple and stupid :)) Remark in IFNA's help: - If value is an array formula, IFNA returns an array of results for each cell in the range specified in value ... and wee neeed it ( kind of "instant population") in array formulas :)))

[@excelisfun]

@@viktorasgolubevas2386 I am going to make a video about your cool formula! Did you discover this formula, or see it somewhere else?

[@excelisfun]

I added your formula to the download file so the whole Team has access. Thank you, victor!!

[@viktorasgolubevas2386]

@@excelisfun while experimenting with the product of SEQUENCEs (remarkably interesting in combination with MMULT), I just looked more precisely at what they spill ... and N/A started to annoy me :)

[@thomasarandt5677]

Valeu!

[@excelisfun]

Thank you, thank you, thank you!!!! Your donation really helps the Team!!! Thanks, thomasarandt5677!!!!!

[@Henrik.Vestergaard]

bum, 26 pdf paged printet - and I who was in dought of choise of summer reading, no more.

[@excelisfun]

Yes: no more better reading than Excel reading!!! Each of the 22 videos in this class has pdf notes to make an epic summer reading : ) : ) The intent is really to watch the video and then check out pdf notes. If you want a straight read, then get one of my books ; )

[@Henrik.Vestergaard]

@@excelisfun 'Microsoft 365 Excel: The Only App That Matters': A must have on every Excel lovers bookshelf

[@LeHoangDe]

You can guide to bold and underline "Total"

[@excelisfun]

I do not understand what you are trying to communicate. What do you mean "guide"?

[@LeHoangDe]

@@excelisfun how to format line total?

[@excelisfun]

@@LeHoangDe I have showed it in this class many times already. But here you go: 1) Highline whole range with Active Cell in upper left range , open conditional formatting to add a formula, then with the cell in the upper left (let's say B12, create formula B$12="Total", the add formatting.

[@excelisfun]

@@LeHoangDe This video: kzbin.info/www/bejne/iXimaYqvit9jmKc at the .21:50 minute mark.

[@excelisfun]

@@LeHoangDe Are you subscribed? I'll try to make short video tomorrow...

[@rustyrogers5583]

Can someone point me to some information on the purpose of a double comma in an Excel formula?

[@excelisfun]

I have no idea what "double comma in an Excel formula" means. But double comma in an Excel built-in function is used when you skip an argument and accept the default.

[@thegreatindianyatri]

Hello sir does array and spill works on excel 2019 edition?

[@excelisfun]

All of this was invented after that version came out. It is impossible to spill in 2019. The only version of Excel that is worth having is Microsoft 365 Excel, even though entities such as mine and many others, do not have it : (

[@zhiqizhang4348]

Is this what Excel can do?

[@excelisfun]

Yes, this is what Microsoft 365 Excel can do : )

[@zhiqizhang4348]

Oh, my GOD!

[@excelisfun]

Having fun? ; )

[@PawelNap]

👍

[@excelisfun]

Glad it helps!!!!

[@zhiqizhang4348]

Sorry, the question I have is from EMT 1671

[@toddb5305]

I have a spread sheet it has 2 columns 1 is dates, the other is Weights. I am trying to find when 3 consecutive dates have the same weight. I have been working on this for 2 months now. I have used IF, CountIf, Or, And, along with a few others. None seem to work. although I have found the sets of 3 the problem is that if the weight appears on a date then the next day is different but the next day is back to that same weight . I get the 2 dates with the blank in the middle. Could you help me on this?

[@excelisfun]

What is it that you want to do once you find the three consecutive dates? List dates? Extract records? Mark with formatting? BTW, this is a random question. This web site is a class video delivery site, not a random Excel question web site. In general, the best site for any type of Excel question is: mrexcel.com/board . I just happen to see this question and so I am engaging.

[@excelisfun]

Are the dates in the date column a unique list? Meaning there are no duplicate dates?

[@faizs9676]

ONE SOUND EXCEL NEEDS TO INCLUDE IN ANIMATIONS AND SOUNDS FOR EXCEL IS ............ ............................................................................. BURRRRRUP ............. I HAVE LEARNT A LOT FROM THAT BOOMERANG GUY ....... ............ I MASTERED INDEX MATCH .............. I DONDT EVEN TOUCH V AND H LOOKUP ........... INDEX MATCH WAS MADE THAT SIMPLE ..... IN ONE INTERVIEW I SOLVED THE QUESTION USING INDEX MATCH ...... INTERVIEWER WAS SURPRISED ..... HE ASKED ME ABOUT V AND H LOOKUP ..... I TOLD HIM I DONT KNOW BECAUSE THERE IS NO NEED FOR ME .............. JUST AWESOME

[@excelisfun]

Glad you did well in your interview : ) : )

[@TheLittleRyeAlchemistintheLigh]

This might sound so cheesy and a bit too much. But dare I say that this world needs more people like you. So generous. So smart. Thank you. You’re a lifesaver.

[@msantosh1220]

Thanks

[@msantosh1220]

Thanks Mike, For me this video is an epic reinforcement of array with some awesome examples.

[@excelisfun]

Thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you, thank you for your very kind and generous donations. They really help, Teammate msantosh1220!!!!!!!

[@msantosh1220]

Thank you for all the support and learnings that you have shared with us and for being a wonderful person Happy Thanksgiving to you and your family. Have a joyful evening

[@excelisfun]

@@msantosh1220 Happy Thanksgiving to you too. I send my love and radness (happy BMX vibes) to you and your family, my Dear Friend and Teammate msantoch1220 : ) : ) : ) : )