Double MINUS??? I am going crazy! This solves a lot of problems with single cell arrays! Thanks to you and to Bill! Great video. CU, Andreas
@excelisfun
3 жыл бұрын
Yes, that is a great and useful trick, Teammate Andreas : ) : ) : )
@bondniko
3 жыл бұрын
I've been using Excel since 1994, but still it is like an endless universe for me. And it is not (only :) because I am dumb.
@excelisfun
3 жыл бұрын
Well, I consider myself dumb too. What you said was partially incorrect. You said: "it is like an endless universe for me". More correctly said might be: "Excel is an endless universe" or "Excel is an endless universe for users". Because it is nearly infinite. There is almost nothing it can not do. This is why the majority of business decisions are made based on results from Excel, and why Excel is the number 1 used business app in the world. We are not dumb because Excel is so wonderfully infinite, we are continually amazed by the new layers of what Excel can do each time we have a different problem to solve : ) : ) : )
@Cutemayankgupta
3 жыл бұрын
I am addicted to your cool excel vids.
@excelisfun
3 жыл бұрын
Not a bad addiction, as being awesome with Excel can get you paid!
@mueez89
2 жыл бұрын
Absolutely amazing! Enjoyed it to the core! Thanks a lot!!
@excelisfun
2 жыл бұрын
Glad you liked it to the core : ) : ) : )
@SyedMuzammilMahasanShahi
3 жыл бұрын
Bill is always so creative with all these difference solutions that he comes up with. Thank him and thank you Mike. Stay blessed.
@excelisfun
3 жыл бұрын
We do have an amazing Team!!! Go Team!!!!!!!!!!!!!!!!!!!!!
@MalinaC
3 жыл бұрын
Tricks + tricks + array functions = AWESOME! Thanks Mike and Bill for sharing!
@excelisfun
3 жыл бұрын
You are welcome for the tricks and tricks : ) : )
@williamarthur4801
Жыл бұрын
Loved the use of choose, informative and lived up the the channel name😊😊
@excelisfun
Жыл бұрын
Glad it all is fun, William!!!
@pedjanbgd4221
3 жыл бұрын
WOW AMAZING SOLUTIONS!, This is best example of CHOOSE and EOMONTH functions, {1,1,3} and {-1,0} BRILLIANT.
@excelisfun
3 жыл бұрын
We do have an amazing Team!!!!!!!!!!!!!!!!!!!!! Go Team!!!!!!
@pedjanbgd4221
3 жыл бұрын
@@excelisfun 👍 You can find very useful formulas on my new KZitem channel. WELCOME Mike!
@excelisfun
3 жыл бұрын
@@pedjanbgd4221 I just subbed to your channel : )
@pedjanbgd4221
3 жыл бұрын
@@excelisfun Thanks Mike, the EAF-4 trick is coming soon!
@Softwaretrain
3 жыл бұрын
Are you kidding me? Boom, you just put amazing stuff from amazing people together. Mr. Excel using array in Eomonth Bill Szysz using Index for total Excel is fun using choose({1,1,2} You will kill me one day with these mind blowing tricks. Really informative and appreciate it. Thanks.
@excelisfun
3 жыл бұрын
NO. No I will not do it, Software Train. I will not kill you someday!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! All the tricks will make you stronger and have the ability to have more fun with Excel : )
@excelisfun
3 жыл бұрын
P.S. I learned {1,1,2} from cr gr0912 : ) : )
@ismailismaili0071
3 жыл бұрын
Mr. Mike you are always the best
@excelisfun
3 жыл бұрын
Always glad to help, Ismail : ) : ) : )
@elbadlis
3 жыл бұрын
Hi Mike, What blown my mind the most is you only use one cell for the whole table report. That is amazing! Thanks for showing us the right way to use Excel. Thanks to Bill too. Shah Alam, Malaysia
@excelisfun
3 жыл бұрын
You are welcome! We have a great Team that we can all learn from : )
@patrickschardt7724
3 жыл бұрын
As usual more great stuff. Thanks. I discovered the EOMONTH array argument problem the other day. This fixes that That CHOOSE trick for the total row is neat. I am watching videos made by the Excel gods
@excelisfun
3 жыл бұрын
That fixed that! (I love that!!!!) Last row CHOOSE IS fun : )
@mohamedchakroun4973
3 жыл бұрын
Another great videos umbelivable what excel can do, to me power query & dynamic arrays are the revolution of Excel
@excelisfun
3 жыл бұрын
Glad you like it, Mohamed : )
@ricos1497
3 жыл бұрын
Awesome Mike and Bill. That choose {1,1....} is world class trickery. One of those: "it shouldn't really work, but it does" type things. I'll be using that one. Edit: also the double negative in eomonth is excellent. I was wrapping it in value(), which also seems to work, but I hadn't considered what the actual issue was, so it's great to know.
@excelisfun
3 жыл бұрын
You are absolutely right about: "it shouldn't really work, but it does" , kind of like the IF({1},array) trick we used to do in INDEX to get it to make a function argument array operation in row_index, were we are like: "what, what!?!?!!?!"
@monireachtang712
3 жыл бұрын
This is absolutely mindblowing! Thank you for sharing!
@excelisfun
3 жыл бұрын
You are welcome for the mindblowing share!!!!!!
@67duiker
3 жыл бұрын
Mindblowing as usual, It's great to learn something new every week
@excelisfun
3 жыл бұрын
Learn stuff is fun! Glad to help, 67duiker!!!!
@EricHartwigExcelConsulting
3 жыл бұрын
You will have to pardon the loud thud of my jaw hitting the floor after watching this video, but what an amazing video!! I am watching it all over again and taking notes to apply these techniques to my projects and reports. Thank you so much for sharing!
@excelisfun
3 жыл бұрын
No worries on the loud thud. It was a good sound : ) Yes, there are a lot of tricks. The video is always here for reference.
@rrrprogram8667
3 жыл бұрын
Mind blowing videos from super mind blowing teacher...
@excelisfun
3 жыл бұрын
Thanks, Teammate RRR!!!!!
@IvanCortinas_ES
3 жыл бұрын
Prodigious video Mike, like all of them! I hope to have the Lambda function available soon and test its full strength. A pleasure, to be able to watch this video calmly several times. Pure caviar. Thank you very much Mike!
@excelisfun
3 жыл бұрын
I love this: "Pure caviar"!!!!!
@ogwalfrancis
3 жыл бұрын
Mind blower indeed, I have to watch it again and again to figure it out 👍👍
@excelisfun
3 жыл бұрын
Thanks, Statistics Guy Ogwal : ) Isaac says Hi, BTW : )
@ogwalfrancis
3 жыл бұрын
@@excelisfun Hi Isaac, that's great, how old is Isaac now👏👏
@excelisfun
3 жыл бұрын
@@ogwalfrancis 14 1/2 : )
@ogwalfrancis
3 жыл бұрын
@@excelisfun a grown up man nw, 👍👍👍
@excelisfun
3 жыл бұрын
@@ogwalfrancis Not quite yet. He still has some fun kid years left. But then later he can have fun in the man years when he is graduated from college and has a good job and can fulfil his duties and responsibilities, then he can be man : )
@sevagj.b
3 жыл бұрын
Crazy man, I love it Thank to Bill and you, Like always is fun 🤩 .
@excelisfun
3 жыл бұрын
Yes, we have such a great and fun Team!!!!!!!
@hoiyinwan8233
3 жыл бұрын
You are talented beyond believe.
@excelisfun
3 жыл бұрын
Our Team is really wonderful : ) : )
@excelisfun
3 жыл бұрын
Glad you like the videos and the teamwork, Hoi Yin Wan!!!
@vida1719
3 жыл бұрын
Excel is full of surprises, like double negative converting impossible to possible
@excelisfun
3 жыл бұрын
I love this: double negative converting impossible to possible : ) : ) : )
@TheCraigie007
3 жыл бұрын
Job done. Mind blown... Awesome as always... Thanks Mike & Bill.
@excelisfun
3 жыл бұрын
Go Team!!!!!
@TheCraigie007
2 жыл бұрын
@@excelisfun Hi Mike - I revisited this again recently as I do with almost all of your content . I was performing an almost identical calculation over a different data set recently and I tried to do a COUNTIFS() over the array and couldn't get it to work - in your dataset I suppose the closest would be another column with a COUNTIFS() of the Sales Transactions against your Month Criteria - is there a ways to achieve that result ? Any help always appreciated .
@excelisfun
2 жыл бұрын
@@TheCraigie007 What didn't work?
@700997372mp
3 жыл бұрын
great video, can't wait to get the roll out of Lambda functions, this is very cool
@excelisfun
3 жыл бұрын
Yes, LAMBDA cool : )
@dirceushimizuschaack1742
3 жыл бұрын
I love the single cell reports! Thanks for sharing your knowledge!
@excelisfun
3 жыл бұрын
You are welcome!
@billywiswall6233
3 жыл бұрын
Majic that video was cool of full tricks! I’ll be using that emonth array trick tomorrow, thank you!
@excelisfun
3 жыл бұрын
Yes, tomorrow is going to be EOMONTH awesome!!!!!!
@jimfitch
3 жыл бұрын
So much goodness in this! I'm still a bit overwhelmed by LAMBDA, but looking forward to GA so that I can slay the beast & start using it. LET has become my new best friend over the past couple of months. I'm developing a workbook now that will use EOMONTH, so this tutorial is very timely. (Funny how your videos always seem tuned in to my current or near-term needs.) But, the sleeper surprise in this tutorial is double minus. How did I miss that before today? It's a much simpler solution than the work-arounds I've been using. Thanks, Mike! And thanks, Bill!
@excelisfun
3 жыл бұрын
LAMBDA is easy. The fact that you have mastered LET over the last few months - that is much harder than LAMBDA. Well, that is at least how I feel. I love to hear that LET has become your new best friend, and that EOMONTH and the double negative are so useful. I learn about the double negative for these rouge function only a few months ago from a Mr Excel video : ) Really, we are only bumping into new problems like these because spilled arrays are relatively new and we are all still getting used to it all : )
@t.pigeon2384
3 жыл бұрын
Apparently for EOMONTH and its sister functions "+" works just as well.
@excelisfun
3 жыл бұрын
@@t.pigeon2384 and all the cousins too : ) Any math operations that will not change value should work, but -- tends to be fastest.
@jimfitch
3 жыл бұрын
@@excelisfun You’re right - LAMBDA itself looks to be easy. What I meant to say was that your demonstrations of LAMBDA are overwhelming, especially the uber-complex single-cell formulas. Overwhelming, but also inspiring!
@t.pigeon2384
3 жыл бұрын
@@excelisfun good to know. Thanks.
@markpodesta4605
3 жыл бұрын
Thank you Mike. Good to include totals at the bottom of dynamic arrays.
@excelisfun
3 жыл бұрын
You are welcome for the fun and totals : ) : )
@GeertDelmulle
3 жыл бұрын
Awesome video, Mike! OMG! :-) So many tricks!! Double negative to enforce vectorization on scalar arguments (that is a BIG one!), INDEX over-stretching, IFERROR+add.CHOOSE to fill up the gaps (where’s the time where adding total rows was hard. I remember it was not that long ago... ;-),... WOW!...
@excelisfun
3 жыл бұрын
That is suuuuuuch a funny and great point: back in the day when adding totals was hard... and now we have many methods: IF, IFS, SWITCH, IFERROR, IFNA, INDEX with too many... So many to CHOOSE from : )
@GeertDelmulle
3 жыл бұрын
@@excelisfun LOL!! :-)
@excelisfun
3 жыл бұрын
@@GeertDelmulle Lucky us : ) What a Team!!!!!!!!!!!!!!!!!
@wayneedmondson1065
3 жыл бұрын
Awesome video and formula! Thanks Mike and Bill for another humbling, yet inspiring example of EXCEL wizardry and mastery! You guys always manage to break the mold. Thanks for sharing your knowledge :)) Thumbs up!! PS - The double unary trick is gold.. thanks for the reminder of that one. Also, want to copy that list of 2007 TookPak functions to remember when to use it!
@excelisfun
3 жыл бұрын
I just add a worksheet tab in the download workbook for you with function names. I got the list from Mr Excel : )
@wayneedmondson1065
3 жыл бұрын
@@excelisfun Excellent! Thanks very much. Will download again and add to my resource archive. Thanks for always being so generous with your time and help :)) Thumbs up!!
@excelisfun
3 жыл бұрын
@@wayneedmondson1065 Go Team!!!!
@gregorymiller6026
2 жыл бұрын
Simple mnemonic for comma vs semicolon in hard-coded arrays: comma for columns semicolon for stacks
@excelisfun
2 жыл бұрын
That is a great trick!!!
@AhmedAbdalalim
3 жыл бұрын
This really mind blowing tricks
@excelisfun
3 жыл бұрын
Glad to blow the mind in a good way, Ahmed!!!
@darrylmorgan
3 жыл бұрын
Boom!These Awesome Formula Tricks Really Did Blow My Mind...Thank You Mike And Bill :):)
@excelisfun
3 жыл бұрын
You are welcome for the blown up mind, in a good way!!!!!!!!!!!!!!!!!!!!!!!!!!
@ashishmohan4707
3 жыл бұрын
Hi Mike very useful video and very practical need everyone for every project ......very big thanks to share with us. Very greatful for us...👌👌👌👌👌👌👌👌💐💐💐💐🎂🎂🎂🎂🎂🎂🎂🎂🎂🎂🎂🎂
@excelisfun
3 жыл бұрын
Big You are welcome, ashish : ) : ) : ) : ) Thanks for the okays and flowers and cakes : )
@excelisfun
3 жыл бұрын
You also get the first place trophy for first comment!!!
@Ratnakumarwrites
Жыл бұрын
Great sir ... Great explanation
@mueezurrehman9266
3 жыл бұрын
As good as it gets! Thanks sir!!
@johnborg5419
3 жыл бұрын
Thanks Mike!!! :) :) This is a great Video. I don't believe I've seen this trick in any of your previous videos and for the Months {-1,0}.....that was Mind Blowing!!! Amazing!!! .... Always Learning!!!
@excelisfun
3 жыл бұрын
It has definitely been in many other function arguments, just not EOMONTH. BTW, I would not have made this video, unless you asked. But that downside is that it took two days to make and that means I am two more days behind on book lol
@excelisfun
3 жыл бұрын
Glad that you are always learning, Formula Guy!!!! Me too!!!!!
@johnborg5419
3 жыл бұрын
@@excelisfun Sorry to have put you 2 days behind. Please do not make me feel Guilty :) :).......but I loved it. That was Monumental !!!!!!!
@excelisfun
3 жыл бұрын
@@johnborg5419 No guilt, I had already set up the workbook to film this, but set it off to the side when LAMBDA came out. But with so many important tricks to share, I had to do it lol
@johnborg5419
3 жыл бұрын
@@excelisfun Never seem to get enough of this video. Watching it again and again and practicing. :) :)
@t.pigeon2384
3 жыл бұрын
Really good stuff learned a lot. One minor suggestion. Since you are reusing the second and third columns of the CR multiple times, I would make them into variables too.
@excelisfun
3 жыл бұрын
I agree. That is a great suggestion. Bill Szysz said that he wanted to try and do it with only two variables, but than I changed it a bit, when I put the d, p, s variables in lol
@Ali.Mostafa
3 жыл бұрын
Just when you think you're using array formulas like a pro! You find out this amazing mind blowing video to learn a loooooot about array formulas 😍😍😍😂😂😂 Mike, you really taught me in this video to stay humble and avoid ego with what I know 😂😂😂 Thank you for every single frame of this brain-feeding masterpiece!
@excelisfun
3 жыл бұрын
You are welcome! I am like you too, Ali! I am just learning too. But together as a Team we can learn so much and keeping come back to videos like this to remind ourselves of these tricks. I do that too, I came back and watch later. The time hyperlink table of contents helps when I am using the video as a references for later. Have you seen the time hyper linked table of contents in Show More area?
@Ali.Mostafa
3 жыл бұрын
@@excelisfun I do, and I like it sooo much... Not just an amazing video, but also an organized system where we always have the workbook to download and the time index for easier navigation! I really like it so much and always refer my friends and colleagues to Excelisfun whenever they ask me how did you learn excel 👍🏻
@excelisfun
3 жыл бұрын
@@Ali.Mostafa Thank you so much for your support!!! I depend on Teammates like you : ) : ) : )
@vandalo7494
3 жыл бұрын
I have been working with excel for 27+ years and I just updated to office 365 and all this stuff in this video are ... Seriously, are we still talking about excel? ;) Damn it, I should have upgraded sooner.
@excelisfun
3 жыл бұрын
Yes, the past 10 years of Excel history are profound. How we did things for 40 years is all different now. From Power Query, Power Pivot, DAX formulas, Power BI, Spilled Array Formulas, LET, LAMBDA and sooooo much more. It will take us a while to get used to it. i am working on a book that introduces all the new Excel for all the things we have done for 40 years. It will be out next year. I will also have a full free video class here at KZitem (starting to make next year) about how people like you and I who know the old Excel can use the new Excel with MUCH more efficiency and accuracy. It is taking a long time for the book, but next year...
@vandalo7494
3 жыл бұрын
@@excelisfun I barely can't wait for it. I learnt a lot with your CTRL+SHIFT+ENTER book (I bought the green-yellow cover one) and made a big "revolution" at the office I work for, with all that functions and techniques you taught in that book. So, a new "revolution" is about to come with that new book you will publish. By the way, do you have any idea when we can pre-order it? Thanks in advance. A since 2013 fan boy. :)
@excelisfun
3 жыл бұрын
@@vandalo7494 I am glad to hear that the green and yellow Ctrl + Shift + Enter book was so transformational!!! : ) My new book is about six months behind my original publish date, probably not out till next Oct 1. You can pre order at Amazon: www.amazon.com/Microsoft-365-Excel-Calculations-Analytics/dp/1615470700#:~:text=See%20this%20image-,Microsoft%20365%20Excel%3A%20The%20Only%20App%20That%20Matters%3A%20Calculations%2C,Insight%20Paperback%20%E2%80%93%20October%201%2C%202021&text=This%20is%20a%20book%20about%20Microsoft%20365%20Excel%2C%20or%20Excel%20365.
@sasavienne
3 жыл бұрын
Just more than amazing. Thanks Mike
@excelisfun
3 жыл бұрын
Just, you are welcome, K D : ) : ) : )
@simfinso858
3 жыл бұрын
Boom Awesome video.Thanks to pivot Table Option.😉😂.This is tuff for me.
@excelisfun
3 жыл бұрын
Yes, why NOT just use a PivotTable!!!!!!!!!!!!!!!!!!!!!!!
@mattschoular8844
3 жыл бұрын
I am going to have to "eat this elephant one bite at a time". This video is jam packed full of great information. Thanks Mile Bill J. and Bill S. for sharing. By the way....is somebody procrastinating on writing an awesome book..... from BMX Sundays to writing the most complex single cell Dynamic array formulas using Let, Lambda, curly bracketsetc? Just kidding Mike... Enjoy your days and keep up the fantastic work, it is much appreciated.
@excelisfun
3 жыл бұрын
The BMX is a Sunday morning few hour must to get exercise and fuel for the brain - so that is good for the book. However, producing videos is usually not good for the book writing because it takes so much time, at least one hour per minute to make a video. I really should just shut off making videos, but I feel obligated to make at least one video a week, I mean for almost 13 years I have been consistent with doing that. Thanks for noticing, Matt. And... You might be a mind reader: this video in particular took two days and gave me fits as I had to re-film many parts of it over and over... : ( and I knew before I started that I should not do this video and instead just write the book...
@mattschoular8844
3 жыл бұрын
@@excelisfun It's all good Mike, I understand.... as I procrastinate from what I really need to be working on right now. I
@excelisfun
3 жыл бұрын
@@mattschoular8844 : ) : ) : ) : )
@excelisfun
3 жыл бұрын
@@mattschoular8844 Go Team!!!!!!
@vijaysahal4556
3 жыл бұрын
superb mike 👍🏻👍🏻👍🏻💯💯💯👌🔥🔥
@chriswebb4797
Жыл бұрын
Awesome video!
@KamleshKumar-lg9xo
3 жыл бұрын
Congratulations sir for 700k subscribers 🎉🎉🎉🎊👍
@excelisfun
3 жыл бұрын
Thank you very much, Kamlesh : )
@jacek.kalinski
3 жыл бұрын
It was amazing! Thanks for this film.
@excelisfun
3 жыл бұрын
ZYou are welcome for the film, Jacek!!!
@davorercegovac3674
3 жыл бұрын
Magic Mike :) Thank you 👍
@excelisfun
3 жыл бұрын
You are welcome, Davor : ) : ) : ) -- excelis "Magic Mike" fun ; )
@yulinliu850
3 жыл бұрын
So cool 👍
@excelisfun
3 жыл бұрын
Glad it is cool for you!!!!
@mmcmillan1999
Жыл бұрын
So sick
@aabubader
6 ай бұрын
Hi Mike Are we can use sumifs(sum_range, criteria_range, Index(--(Month(A1:A10)&Year(A1:A10),,1)) replaced of emonth function not working and this give #value!
@SIEXLExcelyPowerBI
3 жыл бұрын
Hi ExcelIsFun! Mind blowing this trick: CHOOSE({1,1,2} ... ) !!! But is not working when you try another order of columns... like CHOOSE({2,1,1}...), or put the two column array in different place of first one. Seems like kind of implicit intersection... do you know about some combination into CHOOSE for do it without referencing individual columns? I try some option but are "big one": =INDEX(CHOOSE({1,1,2}... ), SEQUENCE(ROWS(range)), {3,1,2}). Blessings!
@excelisfun
3 жыл бұрын
Yes, I can only get it to work from the front too. I like your comment that it works with implicit intersection. I had not thought of it like that before. I do not know how to use CHOOSE without referencing individual columns.
@chrism9037
3 жыл бұрын
That is awesome, thanks Mike!
@excelisfun
3 жыл бұрын
Glad you like it, Teammate Chris : )
@tywe8746
3 жыл бұрын
Thank you for sharing! Great as always!! May I have one question? I need to pull A Part with 20k, and in the inventory, there is A Part in 01/01/2021, 10k, in 01/02/2021, 5k, in 01/03/2021, 10k. So I need 10k, 5k and another 5k from the inventory. Is there a way by Array formula could pull out these three lines directly? I have hundreds of parts. Thank you!
@nigelbarrett8973
3 жыл бұрын
Great video, loved the IFERROR to get the total row. BTW did you realise you spelt 'argument' as 'argumnet'?
@excelisfun
3 жыл бұрын
Nope. I am a terrible speller. I am dyslexic too. So when I spell words wrong I almost always can not see it. That is why I have soooooo many videos posted with miss spelled words. I depend on kind teammate like you to help me out : ) Thanks, Teammate darryl : ) :)
@ContentedSoul
3 жыл бұрын
Thanks for another great video. I notice that you did not include the header row in the LET formula. With a little twigging of the solution you can include every thing in the one formula:=LET( h,{"Person","EOM","Sales"},p,SalesTable4[Person],d,SalesTable4[Date],s,SalesTable4[Sales], pe,SORT(UNIQUE(CHOOSE({1,1,2},EOMONTH(--d,{-1,0}),p)),{3,1}),seq,SEQUENCE(ROWS(pe)+2,1,0), ts,SUMIFS(s,p,INDEX(pe,,3),d,">"&INDEX(pe,,1),d,"
@excelisfun
3 жыл бұрын
In other videos I included header row in formula, but because it is a hard coded value, it just seemed easier to just type it into formula. However, that also has it's drawbacks... Thank you for your formula that enhances the Team, ContentedSoul : )
@excelisfun
3 жыл бұрын
I added your solution to the download workbook so other Teammates can benefit : ) Go Team!!!!
@mrronl05491
3 жыл бұрын
Mind blown.
@excelisfun
3 жыл бұрын
Blown in a good way, though ; )
@jazzista1967
3 жыл бұрын
HI Mike. This was a great videowith a bunch of great tricks at the same time. Quick question: Is it possible to replicate this solution using sumproduct with dynamic arrays? Every time I try to use a sumproduct and dynamic arrays in the same formula , I always get an error...and i end up using SUMIFS with dynamic arrays instead .Have this happened to you? Thanks for posting
@excelisfun
3 жыл бұрын
SUMProduct will always aggregate to one answer, like SUM function. It is not like SUMIFS that has a criteria argument that you can make a function argument array operation to spill many items.
@usmaniqbal1836
3 жыл бұрын
Great ! 💞
@excelisfun
3 жыл бұрын
Glad it is great for you, usman!!!
@sachin.tandon
3 жыл бұрын
I worked on some ideas further. Here is a generalised solution for both unpivoted and pivoted summaries >> drive.google.com/file/d/1GCZ90nC-l8qlZrcyUfic-Shj0RcAE5xd/view?usp=sharing --> using LET, (hard part is appending arrays elements either sideways or vertically) - each LET can be wrapped in a LAMBDA to pass in external variables - allowing for even greater solutions!
@DavidNBerger
3 жыл бұрын
Hero.
@excelisfun
3 жыл бұрын
Go Team!!!!
@rrrprogram8667
3 жыл бұрын
The worst thing is... As a cost cutting measure... Our company has moved from office 365 to office 2016... Bloody hell
@RogerStocker
3 жыл бұрын
It can be worst, look to our company, we have "365 Plus" but our IT is still blocking the rollout for array-functions-update.... 😢
@rrrprogram8667
3 жыл бұрын
@@RogerStocker lolll
@excelisfun
3 жыл бұрын
That is terrible. Don't they know that the Excel 365 costs incur lead to revenue saving efficiencies !?!?!?:!?
@excelisfun
3 жыл бұрын
@@RogerStocker That IS the worst : (
@ozgur937
3 жыл бұрын
How could you cut cost if you have active 365 subscription, something doesn't add up.
@GaryMillerUK
2 жыл бұрын
Very interesting, but not enough on HOW it works. Why does {1,1,2} work?
@excelisfun
2 жыл бұрын
Well I do have over 3,500 videos and have explained the basics of array formulas in a number of previous classes, but {1,2,3} is an array constant that says please get the 1st, 2nd and 3rd values. In array syntax {} house the array, , indicates column, ; indicates row. So {1,2,3} delivers the three answers simultaneously across the columns.
@HusseinKorish
3 жыл бұрын
This is amazing ... but ... i'm still stuck in let function .
@excelisfun
3 жыл бұрын
Sorry about the getting stuck... But at least the EOMONTH tricks are useful everywhere : )
@HusseinKorish
3 жыл бұрын
@@excelisfun of course ... Everything is amazing
@excelisfun
3 жыл бұрын
@@HusseinKorish : ) : ) : )
@registryfree22
3 жыл бұрын
Hello. Could you please help me in finding a response to my inquiry that is how can i generate excel workbook from templates ? Is there any free add-on can help? Or something like that. Thanks in advance
@excelisfun
3 жыл бұрын
I do not know how to do that, as I do not use templates. Try posting question here: mrexcel.com/forum
@georgsterni1092
3 жыл бұрын
Again a video after which I feel that I hardly know anything about Excel 😅
@excelisfun
3 жыл бұрын
But we are all like that. None of us know much. That is why it is so awesome to be part of a team. Together we know much more : ) Go Team!!!
@jerrydellasala7643
3 жыл бұрын
=LET(url,"excelisfun.net/files/EMT1707.xlsx",lft,LEFT(url,5),IF(lft="https","Good Download","Download Problem")) (Still the BEST Excel videos on KZitem!)
@VivekSingh-ig3bn
3 жыл бұрын
its not working in my excel 2019 with your ref file eom function help! me
@excelisfun
3 жыл бұрын
Microsoft did not put the spilled array functionality in Excel 2019. It is only in Microsoft 365 Excel.
@excel_free
3 жыл бұрын
700 k 👍👍👍
@excelisfun
3 жыл бұрын
Thanks!!! I just subbed to your channel : )
@ozgur937
3 жыл бұрын
is this overkill, is this unnecessary, dont know, looks cool, but kinda missed good old sumproduct magic
@excelisfun
3 жыл бұрын
No worries, Excel is beautiful that way, if we want to use SUMPRODUCT, our dear Excel will let us : )
@jazzista1967
3 жыл бұрын
Hi Ozgur and Mike! Is it possible to replicate this solution using sumproduct with dynamic arrays? Every time i try to use a formula with sumproduct and dynamic arrays in the same formula , i always get an error...Have this happened to any of you?
@ozgur937
3 жыл бұрын
@@jazzista1967 Sorry never tried.
@soheiljafari
3 жыл бұрын
Hi, I found simpler solution, how can I send mine for your review?
@excelisfun
3 жыл бұрын
Post formula here! There are a lot of solutions posted so far in the comments - join the Team, Soheil!!!!
@excelisfun
3 жыл бұрын
There are also four other videos that have solutions posted in the comments, EMT 1701-1703 and 1705 : )
@excelisfun
3 жыл бұрын
Can you post? We want to see : ) : )
@mohamedamr8081
3 жыл бұрын
I hear you mike but that just sounds like a nightmare to audit those formulas. I think it is always better to break down stuff like this or just create these reports with PQ, all you would have to do is update the source (or make it dynamic), this would still be simple for laymen. This seems to be overly complicated and either for bragging rights or over complicate things to make it look harder.
@excelisfun
3 жыл бұрын
The is some truth to what you say. But think about what Power Query does behind the scenes. The same thing. Let and then lines of code. The advantage to formulas is always only one thing: they are the only tool that updates instantly. And for some models that is mandatory. The advantage to LET is that you can define a single cell, and then with the new LAMBDA, you can make a re-useable function. For some models and situations, it is a dream come true. The other thing about LET is that is removes complex formulas with many formula elements that are repeated. All of this is not for all situations. But it does solve historically difficult situations in the worksheet. And for some people and some models it will be perfect.
@geneduplantier8779
3 жыл бұрын
I need advice on a project how can I contact you?
@excelisfun
3 жыл бұрын
The best place for Excel questions is: mrexcel.com/forum
@lisamccray6481
3 жыл бұрын
Waste $$$ on the lottery?🤔 Watch your wonderful videos?🧐 So, the Lamda and Let function effects the sales report by .....
@brighnquisitive6217
3 жыл бұрын
What did I just see?
@excelisfun
3 жыл бұрын
Crazy Array Formula Fun. Plus this can be a one-stop shopping video spot for crazy array tricks that you can come back and watch regularly.
@gelert64
3 жыл бұрын
'Argument' lol
@excelisfun
3 жыл бұрын
As I posted below: I am a terrible speller. I am dyslexic too. So when I spell words wrong I almost always can not see it. That is why I have soooooo many videos posted with miss spelled words. I depend on kind teammate like you to help me out : ) lol
@gelert64
3 жыл бұрын
@@excelisfun amazing videos so grateful to see them :-)
@excelisfun
3 жыл бұрын
@@gelert64 : )
@AnilKumar-jo6ug
3 жыл бұрын
Nobody tells How to calculate in Excel Step Up SIP Amount for a Target Corpus ? Everybody tells big big things about Step Up SIP. Can you at least tell how to calculate it in Excel with using a formula ?
@excelisfun
3 жыл бұрын
This is all I know: kzitem.info/news/bejne/k6uLnGeioZV6lX4
@AnilKumar-jo6ug
3 жыл бұрын
@@excelisfunThanks a Lot. But I require vice versa, means, say Final Sum (Corpus to collect in 10 years) is $2,00,000 and I want to know monthly SIP with yearly growth of 10% ? Can I use GoalSeek() function of excel to find exact value of Monly SIP amount, instead of trial and error method by so many iterations to find the Final Monthly SIP? Please Guide.
@ogwalfrancis
3 жыл бұрын
Mind blower indeed, I have to watch it again and again to figure it out 👍👍
@excelisfun
3 жыл бұрын
That is what is great about videos - you can watch again and again : )
Пікірлер: 253