Thank you! I am still waiting for GROUPBY to arrive in my Excel version so this was a PERFECT solution until then. So grateful for your easy to understand and functional examples!
@elbadlis2 жыл бұрын
I challenged myself to do it with single cell formula, and here is my formula. =LET( c,VSTACK(UNIQUE(VT[Video]),"Total"), cc,COUNTIFS(VT[Video],DROP(c,-1)), ccc,cc/ROWS(VT[Video]), HSTACK(c,VSTACK(cc,ROWS(VT[Video])),VSTACK(ccc,SUM(ccc)))) Thank you, Mike!
@excelisfun2 жыл бұрын
Beautiful!!!!!! You and I love the single cell, but this video is for my Highline College basic stats class, so the single cell report is too much for them...
@iankr2 жыл бұрын
I'll TAKE that VSTACK when it arrives and DROP the old-school method! Great stuff.
@excelisfun2 жыл бұрын
Yes!!!! The new function add so much new ability. It's like Christmas : )
@darrylmorgan2 жыл бұрын
Boom!Great Class,The More I See Of The New Functions The More Excited I Get To Have Some Fun With Them...Thank You Mike :)
@excelisfun2 жыл бұрын
Me too: the more we play, the better it gets!!!!
@KevinPGA2 жыл бұрын
Incredibly clever use of brand new functions!!!
@excelisfun2 жыл бұрын
Thanks, Kevin!!!! They definitely make spilled reports so much easier : ) : ) : )
@mohamedchakroun49732 жыл бұрын
Amazinggg Mike what a life become so easy with dynamic arrays
@excelisfun2 жыл бұрын
What you said is sooooo true : ) : ) : ) : )
@mattschoular88442 жыл бұрын
These options are fantastic. Thanks Mike
@excelisfun2 жыл бұрын
Always good to have options : ) Especially EXCELlent ones ; )
@Excelambda2 жыл бұрын
Great Video!!✌Using TAKE for % was super cool!!!✌😉
@excelisfun2 жыл бұрын
Thanks, ExcelLambda. The next one will be even better ; )
@GustavoSoares2 жыл бұрын
Excel was already amazing, but every new function is just incredible.
@excelisfun2 жыл бұрын
Incrementally better with each new function : )
@asmundbratlie34342 жыл бұрын
Your videos - both old and new - are prime content for anyone who needs to learn anything Excel- og Power[input your POI]- related! GG
@excelisfun2 жыл бұрын
I am always glad to help! Thanks for your kind words, Asmund!! I love this: learn anything Excel- og Power[input your POI]- related : ) : ) : )
@johnborg54192 жыл бұрын
Thanks Mike. That was fun!!! I couldn't see a good use for Take and Drop. Now it makes sense!!! Thanks again. :) :)
@excelisfun2 жыл бұрын
Yes, they are perfect for just these situations. TAKE and DROP will almost always be for dropping or taking from single objects like a full table or a full column or even taking a certain number of items for calculations liked trimmed mean.
@SyedMuzammilMahasanShahi2 жыл бұрын
Thanks for sharing this EXCELlent video Mike.
@excelisfun2 жыл бұрын
You are welcome, fellow teacher!!!!
@wayneedmondson10652 жыл бұрын
Awesome Mike! So nice to be able to play and practice with these before official release :)) Thanks for another great demonstration of how to mix and match the functions for creative and useful results! Thumbs up!!
@excelisfun2 жыл бұрын
You are welcome for the mix and match play for useful results, Wayne!!!!! Go Team!!!!!
@excel_along_the_way2 жыл бұрын
concipimus, computamus, praesentamus we imagine, we calculate, we present Thank you Mike
@excelisfun2 жыл бұрын
You are welcome, Lester!!!!
@nadermounir82282 жыл бұрын
This is a phenomenal video Mike 📹 absolutely brilliant 👏 👌
@vuho46682 жыл бұрын
Just what im looking for. Thank you very much
@Jill_Liu2 жыл бұрын
what a great video.
@excelisfun2 жыл бұрын
Glad you like it, Jill Liu!!! When will you post your first video at your Analytics KZbin Channel?
@மின்விரிதாள்_விரிப்போம்_வாங்க2 жыл бұрын
Yes! With the new functions, possibilities are infinite!
@excelisfun2 жыл бұрын
The sky is the limit! Infinite fun for us Excel Lovers!!!!
@BaniMoniah2 жыл бұрын
Can’t wait to get those new functions 😃
@excelisfun2 жыл бұрын
I can't wait for you to get them either : ) : )
@chrism90372 жыл бұрын
This was great, thanks Mike!
@excelisfun2 жыл бұрын
You are welcome, Chris M!!!!!
@henryg57352 жыл бұрын
In the case of the example, I think i preferred "old school"; very neat solution. Both were really.
@excelisfun2 жыл бұрын
I actually would use the 1st column from first solution and second column from the second solution, a mix of both!!!
@richardhay6452 жыл бұрын
Great demonstration/Illustration of TAKE and DROP. Homework for MS: permit Number Formating (as with Pivot Rables) in spreadsheets!! Cell formatting no longer enough. And whil they are at it all tabs accessed by Ctl+1 need ti be available to folliw migrating text and numbers.
@excelisfun2 жыл бұрын
I hope that all comes to fruition soon : ) : ) : )
@JoseAntonioMorato2 жыл бұрын
Dear Mike, To keep everything in one cell: =LET(col_1,VSTACK(SORT(UNIQUE(VT[Video])),"Total"), col_2,VSTACK(DROP(COUNTIFS(VT[Video],col_1),-1),ROWS(VT[Video])), col_3,col_2/TAKE(col_2,-1), VSTACK({"Video","Days","% of Total"}, HSTACK(col_1,col_2,col_3)))🤗
@TheDirge692 жыл бұрын
amazing...thanks Mike..
@excelisfun2 жыл бұрын
You are welcome, Dirge!!!!!!!
@patrickschardt77242 жыл бұрын
Things that were still a little complicated or at least long with LET, are ow even easier with these new functions I hope Microsoft continues to listen to users and their submissions of custom functions and continues to create new official functions from them
@excelisfun2 жыл бұрын
I hope so too!!!! Yes, for most users, create spilled reports with a few columns rather than a single cell formula is much easier!!!!
@bagnon2 жыл бұрын
# = Spilled Range Operator
@excelisfun2 жыл бұрын
Hash tags are awesome ; )
@B1897forzajuve2 жыл бұрын
Mike, am I right when saying that this new functions along with LAMBDA, LET and the other Office 365 functions are replacing all combinations of old functions that have been used before Office 365? I am talking about medium or more complexed scenarios and combinations of functions, not a single formulae.
@excelisfun2 жыл бұрын
They are a great addition and they replace some combinations of old functions. There are hundreds of functions and combinations of functions that these will not replace, such as BINOM.DIST.RANGE, XNPV, LOOKUP, and many more. But they really, really help with dynamic spilled array formulas, especially the single cell formulas that we have been trying to piece together since 2018. It is like a whole new easier world : ) : )
@B1897forzajuve2 жыл бұрын
@@excelisfun For someone like me who started learning Excel two years ago, the whole process is whole easier, because I do not need to learn those complex combinations of old functions, at least not all of them. Is there any chance that you can show us examples of financial models created with these new functions?
@excelisfun2 жыл бұрын
@@B1897forzajuve Yes, yes, yes!!! Like so many of the students in my classes, you and they are learn Excel the New School easier way!!!! I do not have new school financial models, but I do have a 110 video financial model class (basics cash flow analysis class at my college) with all the financial functions. And for most financial models you do not need many of the new fancy functions, because the old standby functions like PMT, RATE, NOMINAL, FV, PV, NPV, XNPV, XIRR and so many more are exactly the same in Old School or new school. If you need to check these out, it is a great playlist: kzbin.info/aero/PL90E1F26C7B85E78F
@deepakmirchandani134810 ай бұрын
resp sir, thanks for such a nice and informative video. further , sir how to use drop/take function to drop /take particular rows /col by providing row no say 3,6,10
@Dedhia_Sidhanth2 жыл бұрын
👍
@excelisfun2 жыл бұрын
Glad you like it, Dedhia!!!
@이태근-e3e2 жыл бұрын
i am your excel pan.1528 crosstabular total possible??
@excelisfun2 жыл бұрын
Yes of course. I posted a video a few days ago about this: kzbin.info/www/bejne/Z2i4aZJsl8qmr6s
@vicon17482 жыл бұрын
👏👏👏👏👏👏👍👍
@excelisfun2 жыл бұрын
Thanks for the claps, Vic!!!!!
@KgasS2 жыл бұрын
This time missed the single cell formula. No Problem...one version is LET( vc,VT[Video], vimade,VSTACK(SORT(UNIQUE(vc)),"Total"), dr,VSTACK(DROP(COUNTIFS(vc,vimade),-1),ROWS(vc)), pct,dr/TAKE(dr,-1), hd,HSTACK("Video","Days","% of Total"), VSTACK(hd,HSTACK(vimade,dr,pct)) )
@excelisfun2 жыл бұрын
Awesome!!!! This video if for my basic statistics class, so no single cell reports lol But we love those single cell beauties : ) : )