Really REALLY depends on what you are doing with it. I've written formulas in Sheets that were pages long, and I can do the same with a half dozen lines using a LET formula in Excel.
09:48 =MAP(B2:B13,LAMBDA(r,SUM(B$2:r))) This " SUM(B$2:r) " thing is great. Haven't seen this before. You should have 1.000.000 subscribers instead of 225.000.
Thank you so much for this detailed explanation for each function! I was wondering about this situation… I have a range of numbers that I need to sum so I highlight the whole range and then use BYROW to sum up the ranges for each row. That works like expected. However, I wanted to add an if statement that checks whether the first column is blank or not. If it is, the cell should be blank. If not, it should continue with the summing. You can’t put multiple arrays into BYROW so I didn’t know how to approach this situation. I tried wrapping the BYROW with MAP but that gives me errors. Then I tried doing an ArrayFormula and I got errors. Would you have any suggestions for how I might do this?
In google sheets not work but r referencing to R row =Map(A1:A10;Lambda(r;Sum(A$1:r))) And when i use string instead just letter, formula not work =Map(A38:A42;Lambda(ROW;Sum(A38:ROW)))
@@ExcelGoogleSheets OK I've got it now: in Google Sheets, writing LAMBDA(r,SUM(B$2:r)) forcibly autocorrects to LAMBDA(r,SUM(B$2:R)), where the capital R points to column R. In Google Sheets, it seems, one has to use a variable name inside LAMBDA that can't be confused with a cell/row/column address or any variable/function predefined in the system. Like for example: =MAP(B2:B13,LAMBDA(run_tot,SUM(B$2:run_tot))) which works correctly. Thanks for your quick feedback which made me insist ;)
@@ExcelGoogleSheets And descending further into this rabbit hole: Google Sheets seems to attempt to parse *ANYTHING that doesn't contain an underscore* as a cell reference, even long combinations of letters and numbers. Maybe a feature and not a bug, who knows.
Wow, thanks for clarifying about this.
Very good introduction to MAP function! I find it very helpful. Thanks!
So helpful! Great explanation! Thank you!
i still find google sheets way better with arrays and stuff than Excel, but i'm glad they are working on this
Really REALLY depends on what you are doing with it.
I've written formulas in Sheets that were pages long, and I can do the same with a half dozen lines using a LET formula in Excel.
09:48
=MAP(B2:B13,LAMBDA(r,SUM(B$2:r)))
This " SUM(B$2:r) " thing is great. Haven't seen this before. You should have 1.000.000 subscribers instead of 225.000.
I want to click the Like button a thousand times!!!
It is interesting, I didn't even realize you COULD use cell references within a LAMBDA, I assumed that it only worked with array type references.
Very helpful! Thanks for the demonstration. Thumbs up!!
Excellent video. Clearly explained 👍❤
Thank you so much for this detailed explanation for each function! I was wondering about this situation… I have a range of numbers that I need to sum so I highlight the whole range and then use BYROW to sum up the ranges for each row. That works like expected. However, I wanted to add an if statement that checks whether the first column is blank or not. If it is, the cell should be blank. If not, it should continue with the summing. You can’t put multiple arrays into BYROW so I didn’t know how to approach this situation. I tried wrapping the BYROW with MAP but that gives me errors. Then I tried doing an ArrayFormula and I got errors. Would you have any suggestions for how I might do this?
Perfect explanation thank you very much for your hard work 👍
Thanks teacher ! it helps me a lot
Thanks for such easy explanation sir
Brilliant! Many thanks for explaining so clearly. Subscribed.
👍
Perspicuous. Thanks
Hi. With byrow-lambda do you know how to fix the error offset parameter 4 is -2? It came up all the time and I do know how to get ridículo of it
great! thanks
Amazing. Thank you.
😀
wow. ExcelIsFunny confused me more. it was that simple
For the running total we can also use the Scan function👍
correct!
In google sheets not work but r referencing to R row
=Map(A1:A10;Lambda(r;Sum(A$1:r)))
And when i use string instead just letter, formula not work
=Map(A38:A42;Lambda(ROW;Sum(A38:ROW)))
Thank you 👍👍
Hi. I have the most impossible spilled array task. Can you help? How can I share my file?
👍
Is there something like that in Google sheets?
Not at this moment.
There is now
What if the lambda function returns an array?
Unfortunately it doesn't allow array returns from the functions.
This doesn't work in Google Sheets though, gives a "circular dependency" error.
It works in Google Sheets just fine.
circular dependency means you are linking to cells in the formula that your formula calculates.
@@ExcelGoogleSheets
OK I've got it now: in Google Sheets, writing LAMBDA(r,SUM(B$2:r)) forcibly autocorrects to LAMBDA(r,SUM(B$2:R)), where the capital R points to column R. In Google Sheets, it seems, one has to use a variable name inside LAMBDA that can't be confused with a cell/row/column address or any variable/function predefined in the system.
Like for example: =MAP(B2:B13,LAMBDA(run_tot,SUM(B$2:run_tot))) which works correctly.
Thanks for your quick feedback which made me insist ;)
@@melkerart1793 That makes sense.
@@ExcelGoogleSheets And descending further into this rabbit hole: Google Sheets seems to attempt to parse *ANYTHING that doesn't contain an underscore* as a cell reference, even long combinations of letters and numbers. Maybe a feature and not a bug, who knows.
text
Thanks teacher ! it helps me a lot
👍