Hi Mike.. as usual.. way cool!! For those without LET yet, this will work: =SORT(CHOOSE({1,2},UNIQUE(B8:B47),COUNTIFS(B8:B47,UNIQUE(B8:B47))),2,-1) Thanks for the super Tuesday magic trick with LET. Thumbs up!!
@excelisfun
4 жыл бұрын
Yes, indeed, it does! Thanks Wayne : ) : ) : ) : ) As a long time mega formula guy, I almost like it better...
@excelisfun
4 жыл бұрын
I love your "Old School" "New School" Formula! I just added it to the downloadable workbook.
@wayneedmondson1065
4 жыл бұрын
@@excelisfun Thanks Mike.. I agree :))
@wayneedmondson1065
4 жыл бұрын
@@excelisfun Great!! Especially good for downloaders who don't yet have LET.. as they get =_xlfn.LET( for the formula in cell D8 and a #NAME? error if they try to update the data.
@vida1719
4 жыл бұрын
Variables are great, they make formulas to look so much more tidy
@excelisfun
4 жыл бұрын
Tidy is good : )
@DougHExcel
4 жыл бұрын
LET the knowledge flow...wow what a cool example of how LET works.
@excelisfun
4 жыл бұрын
LET me sat Thanks, Doug H : )
@ogwalfrancis
4 жыл бұрын
Let function is again more useful than i thought it is, thanks so much for showing me how to make use of the let function 👋👋👋👋
@edge5817
4 жыл бұрын
Thank you so much Mike!... that was EXemplary, Catchy, Entertaining & Legendary... I LOVE EXCEL!
@excelisfun
4 жыл бұрын
Yes! We all love Excel!!!!!
@excelbear6860
4 жыл бұрын
wow. i'm amazed by how formulas allow variables now. it's very useful when i used it in Measures
@excelisfun
4 жыл бұрын
Yes, now like Let in Power Query, VAR in DAX and now LET in Worksheet - lots of fun with Variables : )
@MrDrewluna
4 жыл бұрын
that alt+enter is really sweet. i tried ctrl+enter one time and just brush it off
@DarrellWade
4 жыл бұрын
Wow ... This is a GREAT video! Can't get over it ... must watch again right now.
@excelisfun
4 жыл бұрын
Me too, I created Frequency Distributions all the time, and this is the way that I am going to do it from now on ...
@kiwikiow
4 жыл бұрын
LET magic happen in one cell. Thanks Mike 🧙♂️
@excelisfun
4 жыл бұрын
Yes!!! I Love this: "LET magic happen in one cell"!!!! I wonder if the ghost is behind all of this ?
@kiwikiow
4 жыл бұрын
@@excelisfun maybe👻 ;)
@martinhoude3518
2 жыл бұрын
Great! I was finding all kinds of solutions, but none that fit because I was using a FILTER array. Now, it works flawlessly!...except I still need to figure out how to count entries only once based on yet another column...
@nadermounir8228
2 жыл бұрын
Absolutely amazing video 📹. Thank you Mike we learn a lot from u 😀
@patrickschardt7724
4 жыл бұрын
Another great one. I always like how you will make one video tutorial and then quickly make another one based on comments
@excelisfun
4 жыл бұрын
It is sort of fun that way : )
@abdulhabibulla2377
4 жыл бұрын
This is awesome. Thanks for sharing.
@spilledgraphics
4 жыл бұрын
05:00 ohhhhhhhhhhhhhh SNAP!!!!! Mike has gone ALT + ENTERED ! YESSSSSSSSS !!!!!!!!!! Always Neat & Tidy !!! always :D
@pierrebodart4577
4 жыл бұрын
One word...AWESOME!
@excelisfun
4 жыл бұрын
Glad you like it, Pierre!!!
@daleholden
4 жыл бұрын
Great explanation Mike
@excelisfun
4 жыл бұрын
Glad you like it, Dale!!
@simfinso858
4 жыл бұрын
Amazing Let + Bonus Formula
@excelisfun
4 жыл бұрын
Glad it is amazing, Teammate : )
@TheDirge69
4 жыл бұрын
WOW! that is fantastic! would save me so much time...
@excelisfun
4 жыл бұрын
And it instantly updates : )
@pmsocho
4 жыл бұрын
Soo cool!
@excelisfun
4 жыл бұрын
Thanks, Teammate : )
@darrylmorgan
4 жыл бұрын
Boom!Wow Simply Amazing..Thank You Mike :)
@excelisfun
4 жыл бұрын
Thanks for the amazing BOOM, darryl : )
@mr.brownstone5716
4 жыл бұрын
Watching these videos is getting me almost ready to employ a LET function. Almost. ;-)
@excelisfun
4 жыл бұрын
Almost is always fun because it means you are at that exciting moment of the precipice before jumping off!!!!!
@UltimateBargains
4 жыл бұрын
The LET function is defining names, from left to right, each with a scope that is local to the cell in which that LET function appears. Thus, the same LET formula expression can appear in multiple cells, and there is no name conflict in the LET function between those cells.
@fancettd
4 жыл бұрын
Yet another great tutorial, thank you!
@excelisfun
4 жыл бұрын
You are welcome, Devin : )
@JoseAntonioMorato
2 жыл бұрын
Dear Mike, To match the PivotTable results: =LET( u,UNIQUE(B8:B47), c,SORT(COUNTIFS(B8:B47,u),,-1), r,CHOOSE({1,2},u,c), lt,SEQUENCE(COUNTA(u)+1)>COUNTA(u), CHOOSE({1,2},IF(lt,"Grand Total",SORTBY(r,c,1)), IF(lt,SUM(c),c))) 🤗
@excelbelajar
4 жыл бұрын
thank you for the tutorial ExcelIsFun, you're my favorite Excel Guru. I have a question btw, How to enable this Let function? I have Ms Excel 365 but the Let function is not there. thank you
@excelisfun
4 жыл бұрын
Microsoft say in July it will be in all of Office 365. Right now it is only in Insider Edition.
@excelbelajar
4 жыл бұрын
@@excelisfun thank you, can't wait for it
@mohamedchakroun4973
4 жыл бұрын
Veryy amazing Mike :-)
@excelisfun
4 жыл бұрын
Glad you like it, Mohamed!!!
@jerrymiles7804
4 жыл бұрын
Outstanding!
@excelisfun
4 жыл бұрын
Yes, Frequency distributions will never be the same : )
@АлександрКропочев-е7м
4 жыл бұрын
Wow! ))) Mike, it's amazing trick! What can I do?) I ve not completed Old school, you hurry up me with the new engine))) Thanks! BIG Thanks! The only question is HOW?) Magician!
@IvanCortinas_ES
4 жыл бұрын
Supreme. Pure caviar!
@excelisfun
4 жыл бұрын
Caviar. I LOVE that : )
@masterof
4 жыл бұрын
Excellent!
@excelisfun
4 жыл бұрын
Thanks for the EXCELlent comment ; )
@sachinrv1
4 жыл бұрын
"Let" the fun continue with Office 365. Thanks for this wonderful video.
@excelisfun
4 жыл бұрын
You are welcome for the wonder : )
@MalinaC
4 жыл бұрын
LET rules! So as Mike :) Thanks for sharing, Teammate :)
@excelisfun
4 жыл бұрын
You are welcome, MVP Teammate : )
@ricos1497
4 жыл бұрын
I broke the let function after your last video and I haven't used it since! I kept getting a corrupted workbook, returning an XML calc error that was unrepairable. I was just trying to be too clever (also known as stupid) with my formula I think! I like this frequency table, it's a good example of a use case that doesn't become ridiculously complex but with a relatively complex result. I'd probably have created a final "s" variable for the sort and then returned the "s", just for stepping back through.
@excelisfun
4 жыл бұрын
The Rico S I know: clever = clever, not clever stupid !!!!!!!!!!!!!!!!!!!!!!! Yes, I thought of you when I added the r variable, because normally I would just do the first two and then: SORT(CHOOSE({1,2},u,c),2,-1). But clever Rico S would have done 4 : ) : ) : ) : )
@excelisfun
4 жыл бұрын
What is your e-mail, so I can send the first 100 pages of my book for "Rico S" editing? : )
@ricos1497
4 жыл бұрын
@@excelisfun I've sent you an email, to your highline address. Hopefully that's the/a correct one
@davebowman5392
4 жыл бұрын
Thanks Mike
@excelisfun
4 жыл бұрын
You are welcome, Dave : )
@raitup00
4 жыл бұрын
Amazing trick!
@excelisfun
4 жыл бұрын
I agree: LET is amazing : )
@ThepExcel
4 жыл бұрын
waiting for LET to be used with excel 365 normal edition!
@excelisfun
4 жыл бұрын
Microsoft says in July : ) : )
@ThepExcel
4 жыл бұрын
@@excelisfun oh! it's this month!! Thx for your information
@DirkOutdoor
4 жыл бұрын
Thanks for this. Isn't tidy with only 1 "d"? Greatings from Germany! Dirk :-)
@excelisfun
4 жыл бұрын
Probaly. I am nortoriously a badd spller lol
@BillSzysz1
4 жыл бұрын
Thanks Mike :-)
@DIGITAL_COOKING
4 жыл бұрын
Totally amazing for someone who likes statistics, great job Mike ! It's been while, I see you're besy with the new book am I right
@excelisfun
4 жыл бұрын
Yes, and I am so bad at writing efficiently, so one page can take me a day. Writing books is very hard for me. But I want to try and put it all into one book: Worksheet Formula, PivotTAble, Power Query, Power Pivot, M Code, DAX and Power BI... We'' see...
@DIGITAL_COOKING
4 жыл бұрын
@@excelisfun I'm sure it will be great👍🙂 one of the best references for excel
@DM-py7pj
Жыл бұрын
At 4:56 you can see a tie for Count. How can you add a second sort, ascending, on Category?
@johnborg5419
4 жыл бұрын
Thanks Mike. Amazing!!!! : )
@excelisfun
4 жыл бұрын
Formulas are getting better and better with this new Excel. For formula guys like us, it is like a happy holiday all over again because we get to re-learn all the 1000s of things we do, but in this new and amazing way! I know a lot of us in the word rely on PivotTables, but with a report like this this single cell LET is just too amazing... : )
@johnborg5419
4 жыл бұрын
@@excelisfun So True !!! People rely on Pivot Table to get the reports done the easiest and quickest way, but where's the fun in learning? The FUN part is when you build a formula. That's the enjoyment of learning, at least that's the way I see it. Unfortunately, people look for stuff in here to solve their particular specific problem at the time and not looking for new things to learn more!!
@jinasalcedo
4 жыл бұрын
That’s amazing!
@excelisfun
4 жыл бұрын
Glad you like the amazing, Jina : )
@vijaysahal4556
4 жыл бұрын
nice 👍👍👍
@excelisfun
4 жыл бұрын
Glad you like it, Vijay : )
@87CVH
3 ай бұрын
Add another variable for the data range as its duplicated
@Softwaretrain
4 жыл бұрын
=Let(t,"Many thanks",e,"Excel is Fun",t&" "&e)
@excelisfun
4 жыл бұрын
LOVE your comment : ) : ) : )
@duffry
4 жыл бұрын
Bonus bonus tip: name your variables something other than single letters so someone reading it knows what the variable is. That someone will probably be far future you and you will thank yourself.
@excelisfun
4 жыл бұрын
I do agree. And if you have been following me for 12 years you see that I normally use full names. But this naming convention of short one letter names is not uncommon, so I like to show tit once in a while. But I agree: smart names are paramount : )
@shoaibahmedkhan9676
4 жыл бұрын
When array functions like UNIQUE will be generally available?
@excelisfun
4 жыл бұрын
Microsoft says later this month (July).
@shoaibahmedkhan9676
4 жыл бұрын
@@excelisfun hope to see it soon. Have been waiting for almost 2 years since Sep 2018 I guess.
@excelisfun
4 жыл бұрын
@@shoaibahmedkhan9676 Yes, it has been too long, with many Microsoft delays...
@chrisward2668
4 жыл бұрын
I really need to get Office 365!!
@excelisfun
4 жыл бұрын
It is great fun : )
@TreyStegall
4 жыл бұрын
I still don't have access to the LET function. Grrrr. Great video, though!
@excelisfun
4 жыл бұрын
It is only in Microsoft 365. It will never be in any other version. At least that is what Microsoft says. Microsoft says all of Office 365 will have LET and Spilled Arrays by the end of this month, July.
@chrism9037
4 жыл бұрын
LET there be many more EXCEL-lent videos! Thanks MIke!
Sooooooooo much easier now with H and V STACK : ) : )
@87CVH
3 ай бұрын
@@excelisfunadd a variable x at the front of LET for the data range of B8:B47 and place x inside unique and countifs
@rafaelaraujo5988
4 жыл бұрын
Professor, i am amazed with your vídeos! I left a message for you in your Instagram account, check It out please! Thanks for ALL the knowledge
@Tripps87
4 жыл бұрын
The LET function is great, however your variable names are horrible. How is anyone supposed to look at that formula and decipher what the variable names mean?
@excelisfun
4 жыл бұрын
I do agree. And if you have been following me for 12 years you see that I normally use full names. But this naming convention of short one letter names is not uncommon, so I like to show tit once in a while. But I agree: smart names are paramount : )
Пікірлер: 112