Excellent! I've never used scan and reduce functions. Thank you for this class!
@ExcelGoogleSheets Жыл бұрын
scan map reduce are probably my favorite functions, but I think there might be like 10 people in total using those :)
@mr_alderson Жыл бұрын
@@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.
@mr_alderson Жыл бұрын
@@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?
@lpanebr Жыл бұрын
@@ExcelGoogleSheets 11, I've already started using them!
@captoshuragnarok74447 ай бұрын
Awesome! Thank you so much for this tutorial!
@user-un6hx2wq6j Жыл бұрын
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!
@ExcelGoogleSheets Жыл бұрын
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.
@user-un6hx2wq6j Жыл бұрын
@@ExcelGoogleSheets thank you, didn't catch that point while watching, yes indeed its convenient
@christiancoronado Жыл бұрын
Great video content!
@lagigi9073 Жыл бұрын
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 ❤️
@ExcelGoogleSheets Жыл бұрын
Just use SUMIFS instead of SUM and you're done.
@lagigi9073 Жыл бұрын
@@ExcelGoogleSheets Thanks so much! Sorry I missed your comment before, I will add it to my library 🥳
@maureennwin11 ай бұрын
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?
@HusseinKorish Жыл бұрын
Just Perfect ...Many Thanks
@ExcelGoogleSheets Жыл бұрын
Thank you!
@nadermounir8228 Жыл бұрын
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
@ExcelGoogleSheets Жыл бұрын
Good call.
@juliannieto19917 ай бұрын
Awesome
@xaquison Жыл бұрын
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.
@bulbulahmed30984 ай бұрын
Great
@KamranMumtazAhmed Жыл бұрын
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.
@douglasteixeiradeabreu Жыл бұрын
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!
@douglasteixeiradeabreu Жыл бұрын
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!
@ExcelGoogleSheets Жыл бұрын
What's your original formula that you drag down?
@douglasteixeiradeabreu Жыл бұрын
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 )
@ExcelGoogleSheets Жыл бұрын
👍
@douglasteixeiradeabreu Жыл бұрын
eu passo meu dia inteiro no sheets, vai ser muito diveritido mudar minhas planilhas para este conceito incrível sem arrastar. #stopdragging gracias @ExcelGoogleSheets
@rrrraaaacccc80 Жыл бұрын
👍💯
@helmanfrow Жыл бұрын
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?
@helmanfrow Жыл бұрын
In this case REDUCE() could be replace with a simple SUM() or SUBTOTAL(). How is REDUCE() useful in other contexts?
@NSE_Foden Жыл бұрын
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?
@ExcelGoogleSheets Жыл бұрын
Yes, I would use MAP for that.
@NSE_Foden Жыл бұрын
@@ExcelGoogleSheets thanks, I'll have a go at using that and see if I can figure out how to do it.
@Electric-Bob Жыл бұрын
Teacher, can the SCAN Function work with the OFFSET Function?
@upigroup6816 Жыл бұрын
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
@Electric-Bob Жыл бұрын
Teacher, when you were in the Womb, did your mom work with lots of Spreadsheets?
@ryanmathews8648 Жыл бұрын
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?
@ExcelGoogleSheets Жыл бұрын
Use SUBTOTAL function. =SUBTOTAL(9,A$2:A2)
@ryanmathews8648 Жыл бұрын
@@ExcelGoogleSheets It worked. thank you!
@BraveHeart-ht8zfАй бұрын
How if cumulative sum only the same data in column A
@motogoa Жыл бұрын
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 Жыл бұрын
Well, if you try =ArrayFormula(sum($B$2:B)) you'll find out why :)
@motogoa Жыл бұрын
@@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.
@ExcelGoogleSheets Жыл бұрын
It is completely reasonable to settle with =sum($B$2:B2)
@ExcelGoogleSheets Жыл бұрын
The only reason I've mentioned REDUCE is to explain what it does compared to SCAN.
@guranslifes10 ай бұрын
i have a question. did google stole these functions from microsoft or microsoft stole these from google ? or is there some international convention ?😅😅