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?
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.
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.
Very good introduction to MAP function! I find it very helpful. Thanks!
Wow, thanks for clarifying about this.
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.
So helpful! Great explanation! Thank you!
Excellent video. Clearly explained 👍❤
Very helpful! Thanks for the demonstration. Thumbs up!!
Thanks teacher ! it helps me a lot
Perfect explanation thank you very much for your hard work 👍
Brilliant! Many thanks for explaining so clearly. Subscribed.
👍
Thanks for such easy explanation sir
Perspicuous. Thanks
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?
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
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.
Is there something like that in Google sheets?
Not at this moment.
There is now
great! thanks
For the running total we can also use the Scan function👍
correct!
Amazing. Thank you.
😀
Hi. I have the most impossible spilled array task. Can you help? How can I share my file?
Thank you 👍👍
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)))
👍
What if the lambda function returns an array?
Unfortunately it doesn't allow array returns from the functions.
wow. ExcelIsFunny confused me more. it was that simple
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
👍