DROP, TAKE & VSTACK: How They Are Useful! Spilled Frequency Distribution with Total Row. EMT 1788

[@CAKimberlyLewis]

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!

[@elbadlis]

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!

[@excelisfun]

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...

[@iankr]

I'll TAKE that VSTACK when it arrives and DROP the old-school method! Great stuff.

[@excelisfun]

Yes!!!! The new function add so much new ability. It's like Christmas : )

[@darrylmorgan]

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 :)

[@excelisfun]

Me too: the more we play, the better it gets!!!!

[@KevinPGA]

Incredibly clever use of brand new functions!!!

[@excelisfun]

Thanks, Kevin!!!! They definitely make spilled reports so much easier : ) : ) : )

[@mohamedchakroun4973]

Amazinggg Mike what a life become so easy with dynamic arrays

[@excelisfun]

What you said is sooooo true : ) : ) : ) : )

[@mattschoular8844]

These options are fantastic. Thanks Mike

[@excelisfun]

Always good to have options : ) Especially EXCELlent ones ; )

[@Excelambda]

Great Video!!✌Using TAKE for % was super cool!!!✌😉

[@excelisfun]

Thanks, ExcelLambda. The next one will be even better ; )

[@GustavoSoares]

Excel was already amazing, but every new function is just incredible.

[@excelisfun]

Incrementally better with each new function : )

[@asmundbratlie3434]

Your videos - both old and new - are prime content for anyone who needs to learn anything Excel- og Power[input your POI]- related! GG

[@excelisfun]

I am always glad to help! Thanks for your kind words, Asmund!! I love this: learn anything Excel- og Power[input your POI]- related : ) : ) : )

[@johnborg5419]

Thanks Mike. That was fun!!! I couldn't see a good use for Take and Drop. Now it makes sense!!! Thanks again. :) :)

[@excelisfun]

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.

[@SyedMuzammilMahasanShahi]

Thanks for sharing this EXCELlent video Mike.

[@excelisfun]

You are welcome, fellow teacher!!!!

[@wayneedmondson1065]

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!!

[@excelisfun]

You are welcome for the mix and match play for useful results, Wayne!!!!! Go Team!!!!!

[@excel_along_the_way]

concipimus, computamus, praesentamus we imagine, we calculate, we present Thank you Mike

[@excelisfun]

You are welcome, Lester!!!!

[@nadermounir8228]

This is a phenomenal video Mike 📹 absolutely brilliant 👏 👌

[@vuho4668]

Just what im looking for. Thank you very much

[@Jill_Liu]

what a great video.

[@excelisfun]

Glad you like it, Jill Liu!!! When will you post your first video at your Analytics KZbin Channel?

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

Yes! With the new functions, possibilities are infinite!

[@excelisfun]

The sky is the limit! Infinite fun for us Excel Lovers!!!!

[@BaniMoniah]

Can’t wait to get those new functions 😃

[@excelisfun]

I can't wait for you to get them either : ) : )

[@chrism9037]

This was great, thanks Mike!

[@excelisfun]

You are welcome, Chris M!!!!!

[@henryg5735]

In the case of the example, I think i preferred "old school"; very neat solution. Both were really.

[@excelisfun]

I actually would use the 1st column from first solution and second column from the second solution, a mix of both!!!

[@richardhay645]

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.

[@excelisfun]

I hope that all comes to fruition soon : ) : ) : )

[@JoseAntonioMorato]

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)))🤗

[@TheDirge69]

amazing...thanks Mike..

[@excelisfun]

You are welcome, Dirge!!!!!!!

[@patrickschardt7724]

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

[@excelisfun]

I hope so too!!!! Yes, for most users, create spilled reports with a few columns rather than a single cell formula is much easier!!!!

[@bagnon]

# = Spilled Range Operator

[@excelisfun]

Hash tags are awesome ; )

[@B1897forzajuve]

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.

[@excelisfun]

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 : ) : )

[@B1897forzajuve]

@@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?

[@excelisfun]

@@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

[@deepakmirchandani1348]

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_Sidhanth]

👍

[@excelisfun]

Glad you like it, Dedhia!!!

[@이태근-e3e]

i am your excel pan.1528 crosstabular total possible??

[@excelisfun]

Yes of course. I posted a video a few days ago about this: kzbin.info/www/bejne/Z2i4aZJsl8qmr6s

[@vicon1748]

👏👏👏👏👏👏👍👍

[@excelisfun]

Thanks for the claps, Vic!!!!!

[@KgasS]

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)) )

[@excelisfun]

Awesome!!!! This video if for my basic statistics class, so no single cell reports lol But we love those single cell beauties : ) : )

[@ecanalyst]

Google Sheets says the DROP function is unknown

[@HusseinKorish]

Amazing ... thanks Mike

[@excelisfun]

You are welcome!!!!!