Running Total, Array - Excel & Google Sheets, SCAN, REDUCE, MAP

[@kebincui]

Great video,clearly and well explained.Thank you.

[@lpanebr]

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!

[@captoshuragnarok7444]

Awesome! Thank you so much for this tutorial!

[@christiancoronado]

Great video content!

[@ВолодимирКоробка-п5р]

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.

[@ВолодимирКоробка-п5р]

@@ExcelGoogleSheets thank you, didn't catch that point while watching, yes indeed its convenient

[@rrrraaaacccc80]

👍💯

[@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 🥳

[@HusseinKorish]

Just Perfect ...Many Thanks

[@ExcelGoogleSheets]

Thank you!

[@maureennwin]

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?

[@bulbulahmed3098]

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.

[@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.

[@juliannieto1991]

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.

[@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?

[@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

[@Electric-Bob]

Teacher, can the SCAN Function work with the OFFSET Function?

[@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.

[@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

[@Electric-Bob]

Teacher, when you were in the Womb, did your mom work with lots of Spreadsheets?

[@guranslifes]

i have a question. did google stole these functions from microsoft or microsoft stole these from google ? or is there some international convention ?😅😅

[@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

[@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.