@@ExcelGoogleSheets sirr,, can help me i bought a big trouble in combine vlookup, query functions , and arrayformula .. actually thats not simple thinking . need more practice and hard logical . pleaser help sirr.
@@ExcelGoogleSheets I have vertical and database data, on the database sheet I have all the work data. i want to query with arrayformula and sum function with 2 criteria . but this is very difficult to implement , I thought about this for days and have not found the best solution . can you help me sir?
I need to do this but with a condition to run per day 1 to last of the month bases on Month ID. I will crack my head cuz you already took me half way! Thanks lottle! Love the way you explain everything ❤️
Generally this is for those who want a formula that automatically copies down as you add new lines, update data, etc.. If you don't care you can use regular formulas outlined in the beginning of the video.
Excellent explanation. I saw a formula LinkedIn that uses the reduce function to reverse text string and I don't understand the logic behind it. I will then use scan to see how it works. Thanks for this tip
This is fantastic! Learned a lot! Which would I use if I need to move rows often (move a row or add/delete a row) and want the running total to stay the same based on the new order?
Thanks for the video. I understand how the formulae work but the question when to use one over the other one, for example, map vs scan vs regular method.
Oh my! This class was absolutely AMAZING, an unprecedented great learning experience! Let me ask you, in a simple formula that I use the filter passing a reference that I want to bring from another tab, I usually do this frequently, but when I update the base via sql that I pull using the appscript I need to drag the formula again, this it's very annoying, I tried to reproduce your majestic teaching with arrays, but I still don't see how to do it with a filter when I want to bring data from another tab with a relationship key. Could you guide us with a video about array with filters, that would be very helpful! Great teaching video, I leave here my sincere gratitude!
I'm having fun in my training with the map, I shot myself in the foot, I didn't limit the number of rows and my spreadsheet in a few moments accused me that I reached the limit of 10 million cells, wow, that looks like the code appscript javascript! Very happy with your teachings!
my goodness, I already did it! eternal gratitude for that, google is so amazing with sheets and appscript, and you make it within everyone's reach, so you have your chair! Thank you, thank you, keep going, don't stop )
eu passo meu dia inteiro no sheets, vai ser muito diveritido mudar minhas planilhas para este conceito incrível sem arrastar. #stopdragging gracias @ExcelGoogleSheets
If you had a ledger with both income and expenditure columns, and you wanted a running total, could these functions be used to create an array formula for that situation?
this is great info, but I have a scenario that I can't seem to solve for.. I need to running total to work when I filter out rows. It works when all rows are viewed, but everything i,ve tried fails to correctly calculte only visible rows if filtered. I need to see the running total after every transaction filtered. Any thoughts?
These 2 examples using Scan and Reduce are simple examples. It will be more practical if you can provide us with more practical examples like using them in Loan Amortization Table or Depreciation table, in addition to Makearray (). Thanks
i have a question. did google stole these functions from microsoft or microsoft stole these from google ? or is there some international convention ?😅😅
Awesome scholastic venture on how to complicate things, should that be the purpose 🙂 But why not simply apply Occam's razor and settle with =sum($B$2:B2) and/or =ArrayFormula(sum($B$2:B)) ?
@@ExcelGoogleSheets yes, that's my point. We get the same result with =ArrayFormula(sum($B$2:B)) as with the long REDUCE+LAMBDA formula, in Sheets at least - the end total.
Excellent! I've never used scan and reduce functions. Thank you for this class!
scan map reduce are probably my favorite functions, but I think there might be like 10 people in total using those :)
@@ExcelGoogleSheets sirr,, can help me i bought a big trouble in combine vlookup, query functions , and arrayformula .. actually thats not simple thinking . need more practice and hard logical . pleaser help sirr.
@@ExcelGoogleSheets I have vertical and database data, on the database sheet I have all the work data. i want to query with arrayformula and sum function with 2 criteria . but this is very difficult to implement , I thought about this for days and have not found the best solution . can you help me sir?
@@ExcelGoogleSheets 11, I've already started using them!
Great video,clearly and well explained.Thank you.
I need to do this but with a condition to run per day 1 to last of the month bases on Month ID. I will crack my head cuz you already took me half way! Thanks lottle! Love the way you explain everything ❤️
Just use SUMIFS instead of SUM and you're done.
@@ExcelGoogleSheets Thanks so much! Sorry I missed your comment before, I will add it to my library 🥳
I was waiting for the part where you would say what are the benefits of scan, reduce, map. I expect it is connected with sorting. Thank you!
Generally this is for those who want a formula that automatically copies down as you add new lines, update data, etc.. If you don't care you can use regular formulas outlined in the beginning of the video.
@@ExcelGoogleSheets thank you, didn't catch that point while watching, yes indeed its convenient
Awesome! Thank you so much for this tutorial!
Great video content!
We can use relative references in dynamic array formulas; you don't need to fix the cell row as you did (B$2) in the running total.
Excellent explanation. I saw a formula LinkedIn that uses the reduce function to reverse text string and I don't understand the logic behind it. I will then use scan to see how it works. Thanks for this tip
Good call.
This is fantastic! Learned a lot! Which would I use if I need to move rows often (move a row or add/delete a row) and want the running total to stay the same based on the new order?
Just Perfect ...Many Thanks
Thank you!
Great
Awesome
Thanks for the video. I understand how the formulae work but the question when to use one over the other one, for example, map vs scan vs regular method.
Oh my! This class was absolutely AMAZING, an unprecedented great learning experience!
Let me ask you, in a simple formula that I use the filter passing a reference that I want to bring from another tab, I usually do this frequently, but when I update the base via sql that I pull using the appscript I need to drag the formula again, this it's very annoying, I tried to reproduce your majestic teaching with arrays, but I still don't see how to do it with a filter when I want to bring data from another tab with a relationship key.
Could you guide us with a video about array with filters, that would be very helpful!
Great teaching video, I leave here my sincere gratitude!
I'm having fun in my training with the map, I shot myself in the foot, I didn't limit the number of rows and my spreadsheet in a few moments accused me that I reached the limit of 10 million cells, wow, that looks like the code appscript javascript!
Very happy with your teachings!
What's your original formula that you drag down?
my goodness, I already did it!
eternal gratitude for that, google is so amazing with sheets and appscript, and you make it within everyone's reach, so you have your chair!
Thank you, thank you, keep going, don't stop )
👍
eu passo meu dia inteiro no sheets, vai ser muito diveritido mudar minhas planilhas para este conceito incrível sem arrastar. #stopdragging
gracias @ExcelGoogleSheets
👍💯
In this case REDUCE() could be replace with a simple SUM() or SUBTOTAL(). How is REDUCE() useful in other contexts?
How would you do a running balance if you have Deposits in one column and Withdrawals in an adjacent column, both reported as positive values?
If you had a ledger with both income and expenditure columns, and you wanted a running total, could these functions be used to create an array formula for that situation?
Yes, I would use MAP for that.
@@ExcelGoogleSheets thanks, I'll have a go at using that and see if I can figure out how to do it.
Teacher, can the SCAN Function work with the OFFSET Function?
How if cumulative sum only the same data in column A
this is great info, but I have a scenario that I can't seem to solve for.. I need to running total to work when I filter out rows. It works when all rows are viewed, but everything i,ve tried fails to correctly calculte only visible rows if filtered. I need to see the running total after every transaction filtered. Any thoughts?
Use SUBTOTAL function. =SUBTOTAL(9,A$2:A2)
@@ExcelGoogleSheets It worked. thank you!
Teacher, when you were in the Womb, did your mom work with lots of Spreadsheets?
These 2 examples using Scan and Reduce are simple examples.
It will be more practical if you can provide us with more practical examples like using them in Loan Amortization Table or Depreciation table, in addition to Makearray (). Thanks
i have a question. did google stole these functions from microsoft or microsoft stole these from google ? or is there some international convention ?😅😅
Awesome scholastic venture on how to complicate things, should that be the purpose 🙂 But why not simply apply Occam's razor and settle with =sum($B$2:B2) and/or =ArrayFormula(sum($B$2:B)) ?
Well, if you try =ArrayFormula(sum($B$2:B)) you'll find out why :)
@@ExcelGoogleSheets yes, that's my point. We get the same result with =ArrayFormula(sum($B$2:B)) as with the long REDUCE+LAMBDA formula, in Sheets at least - the end total.
It is completely reasonable to settle with =sum($B$2:B2)
The only reason I've mentioned REDUCE is to explain what it does compared to SCAN.