Google Sheets ARRAYFORMULA, Introductions to Arrays, ARRAY_CONSTRAIN, SORT Functions Tutorial
ฝัง
- เผยแพร่เมื่อ 1 ต.ค. 2024
- 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!
I’m mind blown! I’ve moved around columns for years!
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!
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
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...
Got it. It's because I am from Germany and we use the comma as a separator. I have to use '\' instead !
@@OliverPohl That's correct. For more details about syntax differences watch this th-cam.com/video/ljvB2arCsNQ/w-d-xo.html
The best videos I found on google sheets
the best video I found about arrays! finally I understood! congrats!
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!
MUITO OBRIGADO!
Your videos are all great, and this one is even better than most!
=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?
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}
The best channel that makes it very easy to grasp that complex looking stuff. Thanks alot. May I know which mic you are using 😊
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.
Thanks , its great
You, sir, write code like a madman, but make it easy to understand. Respect. Thank you for sharing your knowledge and experience.
Amazing tuts! It's working like magic! Thank you so much.
Salutations to probably the best teacher in the world! And trust me this is not exaggeration!
amazing! thank you so much. this will help me in my reports.
I love❤️ this channel. Thank you so much👍I get to learn a lot.
This is the best channel on google sheets for sure
Excellent training video. Thank you!
Please help me. Why I cannot use this function: ={1,2,3}, whenever I use it show #ERROR!
Depending on your time zone you may need to use \ instead of comma. Try ={1 \ 2 \ 3}
what about ";"? which character should I use?
Straight to the point and useful! Feel happy about thumbing up!
Curiosity is, who disliked this video
The best of the best you give a lot of hope thanks bro
So so so helpful! Thank you. Great lesson in how to use the power of formulas in Sheets.
Glad it was helpful!
@@ExcelGoogleSheets May I know which type of mic you are using?
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?
th-cam.com/video/NM5-bAUP29U/w-d-xo.html
I want to shout it out loud:
THIS IS ABSOLUTE AWESOME!!!
Thank you!
Thanks for sharing. one of the best video.
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)))
Amazing
xlookup allows you to lookup a values to the left without resorting to an array.
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!
I have a question:
I want to know how to separate information out like you have done using different columns (product name, stock number etc)
however my information is grouped together:
cell A1: Nike Air Max
cell A2: 4953-66-590
cell A3: $190.00
cell A4: $148.20
cell A5: blank row
cell A6: Nike Metcon DSX Flyknit
and so on
how do I go about presenting what I showed above where information is grouped and listed downwards as what is shown in the video? many thanks
Amazing! Thank you for this video and explaining the arrays in Google sheets! This has opened me up to more ideas and how to use them 🙏🏽🙌🏽
Great video but very aggressive ads that are very distracting
I know, that this shouldn't be asked here... bit is there a similar functionality in Excel???
Excel does have arrays, and starting with latest version there are also Dynamic Arrays. That being said, they work a little differently & generally are not as flexible as Google Sheets arrays. You can see how Excel dynamic arrays work in this video th-cam.com/video/kf8vaA5Utt4/w-d-xo.html
Is it possible to use an ArrayFormula with the Googlefinance function? Thanks
Hi , thank you so much for this great tutorial !!
1:00 Documentation says that in countries that uses coma as currency separator (my case), substitute comma by inverted slash. Example: ={1\2\3}
Do you have a tutorial for averaging in arrayformula? Let's say for example arrayformula for {D2=avarage from A2, B2, C2 ; D3=average from A3, B3, C3}
ARRAYFORMULA is not visible to me
Great tutorial for the practical use of array formulas. I love that you start with the basics concepts and uses and develop the complexity step by step ...reminds me of K.Stroud's programmed learning technique (ref: Engineering Mathematics).
function array formula same as sumproduct
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.
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.
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!
THANK YOU SO MUCH!!!
I've tried to solve it for three days!
At 6:30 how did you lock the cells so quickly?
Thanks, great video!
F4
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?
OMG! sheets are amazing now!
Thank you for your tutorials. I've been learning a lot from your channel. Thank you so much.
Very gorge pic
make series on finance formula please.
Very helpful, thank you!
Hi, we don’t have these functions in excel do we? If yes, what are they please?
Arrays in Google Sheets and Excel work in different ways.
Thank you so much for the video! It is very useful on introducing array and array formula.
Amazing channel. Amazing author. Great explanation
Gratidão
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.
Simply!!! Wow.
Thanks you so much Sir
👍
Arrayformula apply to number to text format convert in auto mode conversion
Can we not get the link of these sheets man?!
Nice video.
I would definitely donate to you for this information! Send me a link!
Thanks for your support. You can find "donate" link here www.chicagocomputerclasses.com/youtube-learn-google-spreadsheets/
This is really helpful. Well explained. Thank you!
1.5 speed - is a normal
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.
PLs help, how in arrayformula function link range???
in A1= data!A1:C20
F1=Arrayformula(data!A1:C20) this work
F1=Arrayformula(A1) this isnot work
Hello Sir...great explanation on the arrays...can you make a video on how to find moving average of a series of numbers using arrays? Thanks.
HI! How to summarize values in rows if in the table of 400 columns by means of ARRAYFORMULA?
Thank you! Very Helpful!
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.
Very Nice😀🙃😉
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
Thanks for your job. Is it possible to use UDFs in ARRAYFORMULA? I couldn't manage to do that.
Thank you it's very helpful and how to insert a new auto formatted sheet which should be a same formatted sheet as previous, is there any lesson ?
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?
ফমুর্লার লিংক দিলে ভাল হত--
Thanks for your video Extremely useful!
Hi, this is great! What if we have more questions around this? How can we contact you?
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.?
This is great. I love how you break it all down into its steps first. Very helpful. Love it
I wish you guys would make simpler videos for intermediate users
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!
How do I combine an Array formula with a Concatenate formula/display.
I want to concatenate 2 columns but use an array formula so the
concatenate column will simply display the combined columns as the sheet
receives submissions from the associated form. If I just use a concatenate
formula such as =CONCATENATE(C2, "",D2) and drag it down the
sheet as I receive submissions it bumps the formula down and the
submission is not concatenated. I don't want to have to go back to the
sheet and copy & paste the formula as I receive submissions.
I tried: =ARRAYFORMULA(CONCATENATE(C2,"",D2)) but doesn't work.
Try & instead. =ARRAYFORMULA(C2:C & "" & D2:D)
Great video, thanx man.... when can we expect more videos about arrays? Is it better to use Array formulas or Query function for this kind of tasks?
Hey can you do it for Google assistant remember commands pass from Google sheets ,you can do one tutorial.
This is very useful. Thanks :)
:)
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!
Very useful. Thank you so much!
🙏🏻
Does ArrayFormula work with countif?
Depends on what you mean. You can put array as a range I believe.
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.
en.wikipedia.org/wiki/Profit_margin
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.
Example below ...Can Arrayformula sum each row?
data result
1 1
2 3
3 6
dst dst
Technically possible, but very complicated.
Excel has released a bunch of good solutions for this, but in Google Sheets you would need to use some kid of matrix multiplication to make this work.
@@ExcelGoogleSheets Thank you for your info
Very useful and clear tutorial. Thank you so much!
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
oh no! Aggregate does not exist in google sheet
Great tutorial 👍
:)
wow all I can say is... thanks!
What about 2-dimensional tables? Like for example, what if I want to add together column and row in an NxN matrix?