It took a few tries but thanks to your examples I was able to utilize MAP and LAMBDA to establish a running total with two columns (debits and credits) to show the SUM of both. This has resolved an ongoing issue with the need to have a recurring formula. I'm so thankful for your tutorials and look forward to each published video. Here is the MAP formula for anyone interested: =MAP(F4:F,H4:H,LAMBDA(payment,deposit,IF(AND(payment = 0,deposit =0), "", -SUM(F$4:payment) + SUM(H$4:deposit))))
You are an absolute legend! I spent so much time learning about IF/AND functions only for it to break when I tried applying it to an array. I was about to give up until I found this video. Thank you!
3:22 Instead of using “AND” on an array u can use (condition1)*(condition2) or more as the main condition. Also can use for “OR” (condition1)+(condition2) or more. It’s easier to do this using the initial formula logic. And depending on what function you’ll use (IF or IFS) add an IFERROR for empty cells. This works for filters too in google sheet at least
Thank you, so useful. A few years ago I really needed a function like this and I’d have swapped my right arm to be able to do this. It will stay in my bag of useful tricks until next time it’s needed.
I accidentally happened to view this session. Not only that it created interested to view complete session, but also to dig deeper into possible use cases for the same. You're an excellent teacher. Anyone with minimal interest to learn will understand the concepts very well. Thanks. Will try to check on your sessions regularly. 👌
Wow! It is a general solution to the functions that can not be used in arrayformula function. Never bother me to rewrite the function for arrayformula running normally. Makes people no need to wish the google sheets to update the old functions.
Wow! A million thanks! This video definitely saved my life after 2 weeks trying to find a way to array my formula. Very, very helpful! It opened a whole new world for me :) I'm already a fan of your channel.
Excelent video, perfectly explained! Could I use this principle to create an array of an import range formula so it would import all data from a list of google sheet URL that updates with any new url added to the list?
Hi, thanks a lot for this deep dive on the difference/similarities between google sheet & excel. What I find very useful in Google Sheets is that you can use an arrayformula in reference to values as results from spill formulas (as UNIQUE,...). As far as I know this is not possible in Excel, because we need all the time to express the range of cells of references... Am I wrong on that?
Someone just asked how to use IF or AND with Arrayformulas in the Google Sheets sub. Nice to just send your video along to totally solve their problem.
Hey! Thank you so much for your content! It’s really helping me. But I’m trying for some time to find a code to allow me to do this: I have more then 300 clientes. And I need to send emails, record some informations… this need to be actualized from time to time with same criteria, like: date from the next appoint, emails that I have send. But it’s not functional to have 300 clients sheets, so I did a form to filter all those clientes, and put the information that a need. My problem is that the data sheet is with the data horizontally, and a need to add those new information for the same cliente, so I can search for their names and see all… it’s possible??
Hello, can you show me how you can use the function "arrayformula" where you make a calculation using data from a previous row. It works when you use a reference to a next row but not to a previous row. It would be helpful for me. Thanks in advance. I get always the message #REF! My function is =arrayformula((B24:B)-(E23:E)) and I place it on row 24.
Thank you once again, Man !! This one solve me a difficulty I have for years in some of my sheets, in which I couldn't apply arrays before. 🙏 And with your explanations, I sure got it right 😉
It will be a pain to convert all my arrayformulas where I use the addition for OR and multiplication for AND, but very worth and make the formulas easy to read!
Hey, I have a document that contains data of stores and it is divided by months and every month in SHEET. The question is how do I calculate the percentages of all the months and the stores for example to have the name of the store listed and some sort of CHART or something that will show if there is an increase or decrease in percentages. Does anyone know a method to do this? I would appreciate your help, thank you very much. Another question, I hope it's not impudence on my part, let's say I did it this way (as in the picture I attached) and I want to take the data of this store from all months when I have a SHEET for each month, is there a formula that extracts the data from each SHEET by store
@@ExcelGoogleSheets if data is in another sheet tab, how to reference it and what values should change in the formula? does the order matter when listing the arrays and setting the variables? thanks!
Hi, i tried the MAP LAMBDA for the running total, but it resulted in error, it says "circular dependency detected. To resolve with iterative calculation, see file > setting". Would you explain to me what might be wrong?
🔥 First of all - your videos and teaching style is way too cool. Thanks for your efforts. I've come to learn that AND doesn't work with array formulas and I can't seem to get this for my dashboard. So I'm collecting the data via Google Forms and then joining the data into an appointment slot dashboard. I need the date and city to match so that the combined data is accurate per date and location. The formula I have is: = IFERROR(IFS(AND(Data!A2=Appointment!A2:A, Data!B2="London",Data!G""),SUBSTITUTE(JOIN(" ",Data!A2:Data!C2, Data!E2&" •",Data!D2, Data!F2&" •",Data!G2)," • "," "))) ... could you please suggest how I may achieve this because the Appointment Table sits on a different sheet with the respective dates for reference. Many thanks, Imran
Sir, how can I rank using Excel and Google Sheets if there are ties, then give the highest rank to the largest weight and if there is another tie, the process would go again until the tie is broken?
It took a few tries but thanks to your examples I was able to utilize MAP and LAMBDA to establish a running total with two columns (debits and credits) to show the SUM of both. This has resolved an ongoing issue with the need to have a recurring formula. I'm so thankful for your tutorials and look forward to each published video. Here is the MAP formula for anyone interested: =MAP(F4:F,H4:H,LAMBDA(payment,deposit,IF(AND(payment = 0,deposit =0), "", -SUM(F$4:payment) + SUM(H$4:deposit))))
THAT RUNNING TOTAL, I WAS TRYING TO SOLVE IT THIS MORNING FOR HOURS UNTIL MY HEAD NUMBS. NOW YOU SOLVED IT, WHAT A COINCIDENCE. THANK YOU
You really make things clear with programming background. the whole point of this video is those names are reference not values. Thanks.
You are an absolute legend! I spent so much time learning about IF/AND functions only for it to break when I tried applying it to an array. I was about to give up until I found this video. Thank you!
3:22 Instead of using “AND” on an array u can use (condition1)*(condition2) or more as the main condition. Also can use for “OR” (condition1)+(condition2) or more. It’s easier to do this using the initial formula logic. And depending on what function you’ll use (IF or IFS) add an IFERROR for empty cells. This works for filters too in google sheet at least
This is absolutely best and simpkest way. I always use it like this: * or + in array formulas...
Thank you, so useful. A few years ago I really needed a function like this and I’d have swapped my right arm to be able to do this. It will stay in my bag of useful tricks until next time it’s needed.
I accidentally happened to view this session. Not only that it created interested to view complete session, but also to dig deeper into possible use cases for the same. You're an excellent teacher. Anyone with minimal interest to learn will understand the concepts very well. Thanks. Will try to check on your sessions regularly. 👌
🙂
i am really appreciate it i randomly opened it because of "impossible" word", and i know, this is what i really need
Wow! It is a general solution to the functions that can not be used in arrayformula function. Never bother me to rewrite the function for arrayformula running normally. Makes people no need to wish the google sheets to update the old functions.
Wow!.. very informative explanation. Thank you very much....
Glad it was helpful!
Wow! A million thanks! This video definitely saved my life after 2 weeks trying to find a way to array my formula. Very, very helpful! It opened a whole new world for me :) I'm already a fan of your channel.
👍
Excelent video, perfectly explained! Could I use this principle to create an array of an import range formula so it would import all data from a list of google sheet URL that updates with any new url added to the list?
anticipate your excellent video everyday
Extraordinary learning sir
Hi, thanks a lot for this deep dive on the difference/similarities between google sheet & excel. What I find very useful in Google Sheets is that you can use an arrayformula in reference to values as results from spill formulas (as UNIQUE,...). As far as I know this is not possible in Excel, because we need all the time to express the range of cells of references... Am I wrong on that?
Finally I found very helpful video. this solved my problem. thank youuuuuuu
Thanks! Hope you keep making more videos!
Thank You!
Someone just asked how to use IF or AND with Arrayformulas in the Google Sheets sub. Nice to just send your video along to totally solve their problem.
👍
I am waitting for your show👍
👍
Arrayformula RIP! Thank you!
Great explainer!
Is it possible to have iterative calculation using MAP and LAMBDA function?
Thank you very much. Very clear and useful content. So happy to have found your channel!! :)
Use + or * instead of snd/or in arrayformulas to get same result... Much simpler.
can Index-Match be used by this Map-Lambda function?
Great video, as usual. Wondering if this could work with indirect. So far, haven't figured it out. Thanks!!
work in what way?
Hi. Can you do this using MAX FUNCTION TO SPILL as an array based on criteria?
Amazing! Thanks a lot
Hey! Thank you so much for your content! It’s really helping me. But I’m trying for some time to find a code to allow me to do this: I have more then 300 clientes. And I need to send emails, record some informations… this need to be actualized from time to time with same criteria, like: date from the next appoint, emails that I have send. But it’s not functional to have 300 clients sheets, so I did a form to filter all those clientes, and put the information that a need. My problem is that the data sheet is with the data horizontally, and a need to add those new information for the same cliente, so I can search for their names and see all… it’s possible??
Great video as always. 👍👍
Thanks again!
Hello, can you show me how you can use the function "arrayformula" where you make a calculation using data from a previous row. It works when you use a reference to a next row but not to a previous row. It would be helpful for me. Thanks in advance. I get always the message #REF! My function is =arrayformula((B24:B)-(E23:E)) and I place it on row 24.
hide array formulas in header row, add manual array with first row as header text and next row as array formula
What did you mean, please? Can you elaborate, please? Or better show ?
She mean create a matrice in the header
@@mehmetkaya4330 A simple example is something similar to this form...
={"header name"; arrayformula}
@@COD_Danyel thansk
@@CutTiesWithYourCaptor thanks
Thank you so much.💓
Thank you once again, Man !!
This one solve me a difficulty I have for years in some of my sheets, in which I couldn't apply arrays before. 🙏
And with your explanations, I sure got it right 😉
yeah this solve my problem, thanks
Great!
Very nice video 📹 and thank you for this great explanation. It would awesome if you have videos on tbe scan function also
SCAN coming up next week.
It will be a pain to convert all my arrayformulas where I use the addition for OR and multiplication for AND, but very worth and make the formulas easy to read!
Hey,
I have a document that contains data of stores and it is divided by months and every month in SHEET. The question is how do I calculate the percentages of all the months and the stores for example to have the name of the store listed and some sort of CHART or something that will show if there is an increase or decrease in percentages.
Does anyone know a method to do this?
I would appreciate your help, thank you very much.
Another question, I hope it's not impudence on my part, let's say I did it this way (as in the picture I attached) and I want to take the data of this store from all months when I have a SHEET for each month, is there a formula that extracts the data from each SHEET by store
Thank you! Great
👍
Thanks!
is this applicable also to countifs and sumifs? thanks!
sure
@@ExcelGoogleSheets if data is in another sheet tab, how to reference it and what values should change in the formula? does the order matter when listing the arrays and setting the variables? thanks!
Hi, i tried the MAP LAMBDA for the running total, but it resulted in error, it says "circular dependency detected. To resolve with iterative calculation, see file > setting". Would you explain to me what might be wrong?
circular dependency means you use references in your formulas to the same cells where the formula is. This is something you should generally avoid.
Got it, thank you very much
🔥 First of all - your videos and teaching style is way too cool. Thanks for your efforts. I've come to learn that AND doesn't work with array formulas and I can't seem to get this for my dashboard. So I'm collecting the data via Google Forms and then joining the data into an appointment slot dashboard. I need the date and city to match so that the combined data is accurate per date and location.
The formula I have is:
= IFERROR(IFS(AND(Data!A2=Appointment!A2:A, Data!B2="London",Data!G""),SUBSTITUTE(JOIN(" ",Data!A2:Data!C2, Data!E2&" •",Data!D2, Data!F2&" •",Data!G2)," • "," ")))
... could you please suggest how I may achieve this because the Appointment Table sits on a different sheet with the respective dates for reference.
Many thanks, Imran
Sir, how can I rank using Excel and Google Sheets if there are ties, then give the highest rank to the largest weight and if there is another tie, the process would go again until the tie is broken?
what is excel