Excel MAP, BYROW, BYCOL Functions - LAMBDA Array Formulas in Excel & Google Sheets
Vložit
- čas přidán 23. 11. 2021
- How to use MAP, BYROW, BYCOL array function with LAMBDA formulas in Microsoft Excel & Google Sheets.
Excel LAMBDA Function • Excel LAMBDA Function
#excel #map #lambda
Very good introduction to MAP function! I find it very helpful. Thanks!
Wow, thanks for clarifying about this.
Very helpful! Thanks for the demonstration. Thumbs up!!
So helpful! Great explanation! Thank you!
Thanks for such easy explanation sir
Brilliant! Many thanks for explaining so clearly. Subscribed.
👍
Perfect explanation thank you very much for your hard work 👍
Thanks teacher ! it helps me a lot
Perspicuous. Thanks
great! thanks
Excellent video. Clearly explained 👍❤
Amazing. Thank you.
😀
Thank you 👍👍
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?
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.
Use the scan formula instead
wow. ExcelIsFunny confused me more. it was that simple
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.
👍
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
For the running total we can also use the Scan function👍
correct!
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
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.
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
👍