Learn how to use ARRAYFORMULA function in Google Sheets as well as arrays in Google Sheets. This tutorial will cover array syntax, ARRAYFORMULA, SORT & ARRAY_CONSTRAIN functions and other tips and tricks.
Very well-structured. Very "watchable" through the examples. Not too slow-paced, not too fast-paced. Because it's so nicely constructed, it's also easy to traverse through parts that are already known or clear, which makes for a smooth experience watching the video, instead of an "Oh now I missed this", "Oh more irrelevant examples of the same thing", and so on. Noice!
This is the first time I've experienced a video I wanted to like multiple times and subscribe multiple times. Love your teaching methods! Thank you very much.
Holy cow this was so helpful!! thank you! I was able to use Arrayformula to create new columns in my sheet which declares month and quarter without having to populate all rows with the original formula!
That's incredible! I'm also learning Microsoft Power BI, and the calculations we can do with array formulas are a lot similar to the calculations we do with calculated columns in Power BI. Finding out that I can do the same in Google Sheets left me pretty happy. That came totally out of left field. Thank you so much!
In my language the default decimal separator is , (comma) instead of . (dot) So that it makes the matrix formula use the \ (backward slash) as column separator (instead of a comma ,) (The separator for rows remains the same " ; " ) So, for separating columns it would be ={A1:A10 \ B1:B10} It took me some time to find this and I just wanted to share.
Thank you, drow me crazy. I thought I specified my Sheets to be in English but apparently not all the way, there must be some mix up with my other Google settings (set for Swedish). Anyway, now it works, thanks!
Great learning (if only I know half of it). SORT wasn’t explained, so I must assume that it was given some detail previously. But that was what I was looking for. Thanks.
I've recently learned that if you type = in a cell and press Ctrl + Shift + Enter it inserts (ArrayFormula) but I don't understand why it doesn't capitalize everything or why it puts the parenthesis around the function name. Why doesn't it do ARRAYFORMULA()? Anybody have any idea? Is there any other shortcuts that insert functions? Thanks
This was so brilliant!!! Thanks so much. Now I have an issue with sorting static and dynamic data on a tab. Could you please do a tutorial on this as well?
i love the way you explain this.. you are the best..please make videos on stock market. For example how to import data from a website. (my stock prices arent in google finace
Great video, thank you for sharing! In column D, you have "$" sign in there that's left-aligned. Is that special formatting? I'd love to learn how to do that 🙂🙏 Thank you!
How do enter I enter the two curly braces? I trieds to enter them directly via the keyboards (the openung and closing one) entering ={1,2,3} results in an error...
Just this one video completely overhauled my ability to work in Sheets - I really can't overstate how useful this was! I technically understood these concepts, and understood the docs, but actually seeing you working with these cells and ranges made everything so tangible for me. It all just clicked after this video. Thank you for the great content!
Hello, I have a real time scenario, at 21:31 in this video you are hard coding the row number, I would like to know how can we dynamically increment that row number by "1". I have tried "ROW" in side "ARRAYFORMULA" function to achieve this but it is working only for couple of rows and later on it is showing out of range error. Please help me to address this problem. Thanks in advance.
Great tutorial thank you. I have a question for you. I am trying to find and average of a start date and an end date. I have a cell showing the time between the two dates, in the format of day, hour, minute and second, but what I'm looking to do is show an average of the times in one cell. hope you can help.
Hope you can help. Can I use ARRAYFORMULA to wrap a VLOOKUP function? My goal is - based on a name or ID#, I lookup a score on another tab on the same worksheet, but the main worksheet is linked to a Google Form, and the form has email notification to the form submitter. In other words, in exchange for filling up the form, I email their score on a particular activity.
Amazing tutorials. I am trying to create a formula to calculate if the D12 is blank, it will calculate from K12 to AH12 but it seems this part ' regexmatch(D12,"")' is not working ArrayFormula(SUM(IF(regexmatch(D12,""),K12:AH12)))
What about using logical formulas like AND or OR within an arrayformula? For example, I want to check if columns A and B (in each specific row) are TRUE. Is there a way to do this with arrayformula? Right now if I write a formula =ARRAYFORMULA(AND(A:A,B:B)) it will only return 1 result for the entire range. How can I get separate results for each row on each row?
Hello friends, I want to learn all functions for sheets. I am new user for google sheets. Which videos should I start with to go from beginners to intermediate and then finally to advanced. Can someone guide me, please? Is there anywhere prescribed what is the series for learning step by step?
Why does not the formula work = ArrayFormula (INDIRECT (OFFSET (Q1; 1 ;; M2)))? Column Q - list of sheets in the document with a certain cell (for example: Sheet1! H2), M2-their number. The value is read only from the first sheet, respectively from the first element of the array.
I got an error on my formula : ARRAYFORMULA(IF(F9:F="";"";VLOOKUP(F9:F;{C7:C259\B7:B259};2;FALSE))) I couldn't get data from Vlookup after I tried to create a Rake type of range {C7:C259\B7:B259}
When you calculated the profit margin, you may have made a mistake. I realized that because I've worked in the same example recently. To calculate the profit margin, you divided the cost by the price and then subtracted the result from 1. However, if we want to figure out the price given the cost and the profit margin, then we won't be able to get the original value. Price = (1 + Profit Margin) * Cost. In conclusion, we should have done Profit Margin = Price/Cost - 1. I used the ARRAYFORMULA and got the correct result.
thank you so much! That's very kind of you. It made sense to me now. The profit margin is a percentage that represents how much of the revenue is profit. I thought that the profit margin would be the percentage of the cost that, when added to it, gives the price. Sorry for the mix-up! And again, thank you for sharing this valuable content.
@@diegomouratorres7094 The important part is to understand how the formula works. Once you get there, then you can create your metrics the way you want them.
supposed that I want to apply 30% of increase over Cost, I mean populate the 1st col with the same value, and the 2nd with cost, is it possible? I mean to show a column with a new cost. tks in advanced.
Even though you asked this question two years ago, I think it's worth answering it because other people can benefit from that. So, let's say you want to increase the price of each product by 30%. How can you accomplish that? That's really simple: take the current price and multiply it by 1.30. That'll give you the new price, right? Instead of creating this formula for one product and then drag it down for all the others, you may want to use ARRAYFORMULA. Let's say you want to place this new column after column C, so you have the previous price and the newest side by side. The end result would look like this: = ARRAYFORMULA(1.3 * C2:C12). I hope it's helpful!
=COUNTIFS(FILTER('FS November 2021 '!B2:B,LEFT('FS November 2021 '!B2:B,4)="FS11")'FS November 2021 '!AB2:AB,"VALID") What's wrong with this formula...why doesn't it work please help?
Hi, I have a problem: How can I use the arrayformula function with MIN (or MAX) for rows? Example I have to calculate for every row from A to Z for every single row. The result is always one. Is there a simple method? Thanks a lot and good for the videos. I look at you from Italy: o)
I can't think of a way of doing that at the moment. Maybe using some sort of lookup would work, but not sure right now. MAX function will definitely not work.
@@ExcelGoogleSheets 206/5000 Don't worry, thanks. At the moment I find solutions but they are so convoluted ... maybe Google should think of a solution ... maybe I try with small or aggregate. I'll let you know ... you're very good at explaining. Thank you again
how to make in credit debit system look at theese and solve my problem. thanks.. a b c example: credit debit balence 1 34 =a1 2 46 =c1+a2-b2 3 56 =c2+a3-b3 4 45 =c3+a4-b4 I want =arrayformula(c1:c+a2:a-b2:b) how to correct this.?
