Static to dynamic: Excel formulas for reducing manual work

Рет қаралды 15,430

Күн бұрын

Пікірлер: 63

@ExcelOffTheGrid 4 ай бұрын

Sign up for our free Insiders program and get the example file: exceloffthegrid.com/insiders-signup/ File Reference for this video: 0226 Static to Dynamic

@IvanCortinas_ES 21 күн бұрын

You are the architect of spilled-arrays. I love the logic used. Thanks Mark!!

@ExcelOffTheGrid 20 күн бұрын

Arrays completely transformed Excel, but we’ve only just scratched the surface. So trying to bring a more power to more people.

@Excelambda 4 ай бұрын

Great video !! ✌ Alternative for "Days in period" (BYROW): =MAP(B7#,G7#,LAMBDA(x,y,MAX(MIN(y,EOMONTH(C3,0))-MAX(x,EOMONTH(C3,-1)),0))) "Monthly Cost" : =LET(v,F7#/H7#*I7#*(D7#="live"),VSTACK(v,SUM(v))) In general, whenever a vector, vert or horiz , I do not use BYROW or BYCOL, always MAP. There is an intrinsic functionality difference btw MAP and BYROW when vectors (1D arrays) (in formulas, with references they act the same) The simplest exercise to visualize and understand the difference =BYROW(SEQUENCE(4),LAMBDA(x,CONCAT(SEQUENCE(,x)))) delivers: 1 1 1 1 instead of the correct result of MAP =MAP(SEQUENCE(4),LAMBDA(x,CONCAT(SEQUENCE(,x)))) 1 12 123 1234 ✌😉

@ExcelOffTheGrid 4 ай бұрын

Very nice. I never really think about MAP in that way. Great solution. 👍

@ExcelOnTheRoad 4 ай бұрын

Very nice approach with the VSTACK and the Total!

@ExcelOffTheGrid 4 ай бұрын

Thank you Giles. 😁

@houstonsam6163 4 ай бұрын

Fantastic presentation. Your recent work explaining application of lambdas, helpers, and general spilled arrays has given me understanding that I put to work immediately in my job and that in turn is helping us ask and answer questions we've not been able to analyze before. I bet you didn't know you were helping a specialty chemical R&D function improve the operational management of its field technical support work.

@ExcelOffTheGrid 4 ай бұрын

Actually helping "specialty chemical R&D functions to improve their operational management of field technical support work" is my key audience.... ... no wait... actually I have no idea what that is. 😂 But, I'm always happy to help. 😁

@ismaelkourouma5558 4 ай бұрын

I have been looking the solution of Total line 2 years ago without success. I am really excited to seen this now. I always think how to automate the recurrent and boring works so this comes at the right time. Now just need to take time and learn from it. Thanks Mark

@ExcelOffTheGrid 4 ай бұрын

You mentioned this in another post a while back - this is the easy way to do it. Which works when you have single column arrays.

@RonDavidowicz 4 ай бұрын

Another great video showing practical uses for some advanced functions …..or you could just add “regular” formulas to your data table and a totals row.😊

@ExcelOffTheGrid 4 ай бұрын

Tables hold data, they are not for presentation - if you use them for presentation you remove the flexibility they give you (I know it's tempting because it seems the easiest option, but it's more pain in the long run).

@huseyinburaktasci 4 ай бұрын

Fantastic! It is a very advanced example. Thank you Mark!

@ExcelOffTheGrid 4 ай бұрын

You’re welcome. 👍

@X1AbuBadr1X 4 ай бұрын

Really nice ,, the techniques i learned from this Video is so much Really aprecaite your videos Thank you

@ExcelOffTheGrid 4 ай бұрын

Happy to hear that! 😁

@excelrobot 4 ай бұрын

Love it, great example for demonstrating the process of converting something that already existed. The Excel team really needs to make conditional formatting support dynamic Applies To ranges so the formatting can expand with the arrays.

@ExcelOffTheGrid 4 ай бұрын

Hey Erik - I would love to see that. I suspect the technicalities are much harder than we realise.

@Swizzletwice 4 ай бұрын

Excellent video. I love how well he explains functions (why and why not) and wrapping formulas.

@ExcelOffTheGrid 4 ай бұрын

Thank you. I try. 😁

@patrickschardt7724 4 ай бұрын

Wonderful lesson. Showing why things are done a certain way , revisiting logic, and more For the formatting for the main data, that could also semi-dynamic. Just check if a there’s a value in the one of the columns for each row… then apply the needed formatting for each column

@ExcelOffTheGrid 4 ай бұрын

That’s a nice idea. I need to check it out, as I’m not sure of the impact on performance, bloat and scrollbar size.

@mohammedelsakally540 4 ай бұрын

Thank you Mark for your always valuable tricks and content that you are providing to us all time ... much appreciated 🙏 ...

@ExcelOffTheGrid 4 ай бұрын

You're welcome. I'm glad you find them useful. 😁

@KimzCraftz 4 ай бұрын

Great video with some topics I can apply immediately into my work. Thank you.

@ExcelOffTheGrid 4 ай бұрын

Great news. Things you can apply immediately is always the best stuff. 🏆

@Hello-bn2yc 3 ай бұрын

fantastic. Thank you very much. Great content as always

@kebincui 4 ай бұрын

Very creative, excellent as always. Thanks Mark ❤👍

@ExcelOffTheGrid 4 ай бұрын

Thanks Kebin 😁

@alessandragallaogatarossi903 3 ай бұрын

Sensacional!!! Parabéns Mark!!! Adoro seu canal e seu conteúdo! Pena que a cotação reais X dólar não ajuda comprar o seu curso...

@extraktAI 4 ай бұрын

Brilliant, thank you!

@ExcelOffTheGrid 4 ай бұрын

Thank you. 😁

@AccessAnalytic 4 ай бұрын

Excellent 😀

@ExcelOffTheGrid 4 ай бұрын

Thank you! 😁

@JoseAntonioMorato 4 ай бұрын

Dear Mark, Since it's to automate the calculations, I also automated the "End Date" column of the "Date" table: =DATE(YEAR([@[Start Date]]),MONTH([@[Start Date]])+[@Months],DAY([@[Start Date]])-1) 🤗

@ExcelOffTheGrid 4 ай бұрын

Nice. Good work. 😁 EDATE would be another good option.

@JoseAntonioMorato 4 ай бұрын

@@ExcelOffTheGrid In fact, with the EDATE function it gets much better: =EDATE(@[Start Date]],@Months])-1 😃🤗

@chrism9037 4 ай бұрын

Great video Mark!

@ExcelOffTheGrid 4 ай бұрын

Thank you. I appreciate that. 😁

@adhamm5503 4 ай бұрын

You are brilliant!! Thanks brilliant your dynamic reports and power query skills are another level and am learning a lot. Can you please tell how to use lambda& byrow with filter function (more than one criteria).

@ExcelOffTheGrid 4 ай бұрын

You don't need to use LAMBDA for a filter with more than one criteria, you can put both criteria in the same argument. =FILTER(array,(column1="A")*(column2="B")) Check out these videos for some ideas. kzbin.info/www/bejne/aGTan4SEpauNntE kzbin.info/www/bejne/fXbMnmZqg7Gpn9E kzbin.info/www/bejne/d5ikh4CKl7N3jw

@adhamm5503 4 ай бұрын

@ExcelOffTheGrid thanks for responding. In one of your videos about FILTER function you show us how to use LAMBDA&BYROW combination to spill the reasult because filter function doesn't work with dynamic arryas#. So replacing the condition in filter by any variable say r & wrap it in LAMBDA& BYROW will make it spill and solve the issue. What I didn't know is how to do the same but when filter function includes more than one criteria. Do I need to add another variable to LAMBDA? Or it will not work and needs another approach.

@sørenf-l6s Ай бұрын

Great video thanks :) I have search for a solution to get a “totals row” under data from array formulas, this looks great. However I also need a special calculaton (Xirr) based on neighbour columns, end date and the totalvalue. Is there a way to make the “totals row”more flexible? At the moment I just place the “totals row” a lot of rows under the formulas and hide rows between last row with data and and “totals row” with VBA.

@ExcelOffTheGrid Ай бұрын

There are loads of ways to get the total below. Just play around with VSTACK dynamic arrays calculations.

@serdip 4 ай бұрын

Thanks for another very interesting presentation demonstrating advanced Excel features. I was thinking that all of the calculations could be done within the Power Query editor, thus eliminating all formulas and conditional formatting. If that's the case, then there would be no need for a separate report tab. Can you advise when it would not be possible to use Power Query alone to get everything done? Thank you kindly.

@ExcelOffTheGrid 4 ай бұрын

Power Query is a tool for extracting, transforming and loading data. You hold the data in a Table; then calculate on the data. That is the most flexible method. Even if you can do it in Power Query, don't. Use the right tool for the right purpose. Otherwise you will restrict your future options when somebody asks for something to be added.

@ghostofreality1222 4 ай бұрын

@@ExcelOffTheGrid By Using Power Query you are not limiting yourself as you stated above in your reply. Your comment just doesn't make sense to me in my brain. I have never been limited by using power query to do formula's in excel table after. You are still referencing the data from the same table, you still have all the same data once it loads to a table. You can add columns in afterwards to the loaded table still in the excel sheet, so where does the limitation come into play? - I would even say that by doing things in power query it actually is the better solution, and you can always edit your Query if that becomes the need, so you really are not stuck. I do as much as I possibly can in Power Query and then do what I cant do there in the excel table. My limitation is I don't know everything in Power Query so that is my personal limitation, but I learn everyday working with it so that is a win for me in my book. As well there are things that is way easier to do in power query than it is in a table and using formula's. I have been doing things no one else in my workplace can figure out how to accomplish because I use Power Query. I'm just saying Power Query is way more than just a tool for extracting, transforming and loading data, so your comment of downplaying it as a tool not much more capable than those basic things is an injustice to Power Query. You would be amazed at what can be accomplished using it compared to what you can accomplish using vanilla Excel and formula's. I would argue by not learning and really digging into Power Query that is limiting you in a lot of ways.

@DaveIsAtWork-Really 2 ай бұрын

I have a sales data table with columns for Agency, Route, Store, Customer, Sales Day, and Sales Amount. I set up a horizontal spill range for the dates as column headers, and the four other columns as a vertical spill range, both with a 'Total' added as part of a 'stack'. Lastly, I have a formula for the actual sales data that spills both vertically and horizontally. It's rather brutal, but it works. After watching this video, I'm sure there could be an easier way. Build the table header: AGENCY | ROUTE | STORE | CUSTNO | DAYS 1-? | TOTAL =tblSales[[#Headers],[AGENCY]:[CUSTNO]] =HSTACK(TRANSPOSE(TEXT(SORT(UNIQUE(tblSales[DATE])),"mm/dd/yy")),"TOTAL") Build the sorted row headers with TOTAL row at bottom: =VSTACK(SORT(UNIQUE(tblSales[[AGENCY]:[CUSTNO]]),{1,2,3,4}),{"TOTAL","","",""}) Build the table data with totals for rows and columns: =IFERROR( LET( Dates,TRANSPOSE(TEXT(SORT(UNIQUE(tblSales[DATE])),"mm/dd/yy")), Custs,CHOOSECOLS(SORT(UNIQUE(tblSales[[AGENCY]:[CUSTNO]]),{1,2,3,4}),4), VSTACK( HSTACK( SUMIFS(tblSales[AMOUNT],tblSales[CUSTNO],Custs,tblSales[DATE],Dates), SUMIFS(tblSales[AMOUNT],tblSales[CUSTNO],Custs)), SUMIFS(tblSales[AMOUNT],tblSales[DATE],Dates))), SUM(tblSales[AMOUNT])) What could be done better???

@ghostofreality1222 4 ай бұрын

If this is a report and a file gets updated by users or a system then it is best to Query the WB or Sheet where the Data is and then import as a Table. By having your Data in Tables it simplifies a lot of other things you may wish to the data later on. Once you have structured your data into a Table you write the formula once, because it is a Table it uses the column headers and not a specific cell reference, and it spills down for each row with out having to create extra Lambda or those Hash Tag Formula's. - Learn how to structure your Data is probably one of the most over looked steps by most Beginner Excel Users. I can not stress that enough, become comfortable with importing and cleaning your data sets using Power Query and then loading that Data to a Table. Once you have yourself setup right you simply have to use the Refresh/Refresh All under the menu for Data and your information will refresh from the source.

@ExcelOffTheGrid 4 ай бұрын

Exactly - couldn't agree more 👍

@stanTrX 2 ай бұрын

Hello. How can i export my worksheets as new excel files, without queries and data connections. I want to break and clean such features and just send the data itself.

@ExcelOffTheGrid 2 ай бұрын

You'll need to use VBA for that.