Google Sheets ARRAYFORMULA, Introductions to Arrays, ARRAY_CONSTRAIN, SORT Functions Tutorial
Вставка
- Опубліковано 24 жов 2017
- 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.
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!
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!
the best video I found about arrays! finally I understood! congrats!
Your videos are all great, and this one is even better than most!
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).
You, sir, write code like a madman, but make it easy to understand. Respect. Thank you for sharing your knowledge and experience.
This is the best channel on google sheets for sure
The best videos I found on google sheets
This is great. I love how you break it all down into its steps first. Very helpful. Love it
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.
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!
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!
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 been doing spreadsheets since 1987.... this is the coolest thing I've seen so far!
Amazing tuts! It's working like magic! Thank you so much.
Hi , thank you so much for this great tutorial !!
Very useful and clear tutorial. Thank you so much!
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 🙏🏽🙌🏽
Straight to the point and useful! Feel happy about thumbing up!
THANK YOU SO MUCH!!!
I've tried to solve it for three days!
I’m mind blown! I’ve moved around columns for years!
Thank you! Very Helpful!
I love❤️ this channel. Thank you so much👍I get to learn a lot.
Thank you for your tutorials. I've been learning a lot from your channel. Thank you so much.
Very nice explanation of array formula.
Excellent training video. Thank you!
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?
Salutations to probably the best teacher in the world! And trust me this is not exaggeration!
VOCÊS SÃO PERFEITOS!
Thanks for sharing. one of the best video.
Thanks for the video ... just great
Thanks for your video Extremely useful!
amazing! thank you so much. this will help me in my reports.
Mindblowing illustration.
Thank you so much for the video! It is very useful on introducing array and array formula.
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
Much needed video bro. U r amazing
The best channel that makes it very easy to grasp that complex looking stuff. Thanks alot. May I know which mic you are using 😊
Thank you for this tutorial. I've been learning a lot from your channel. Thank you so much.
Happy to hear that!
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?
Very well explained. Thanks a lot.
Very useful. Thank you so much!
This is really helpful. Well explained. Thank you!
Amazing channel. Amazing author. Great explanation
Great video!! Thanks a lot!
Super awsome Bro..
The best of the best you give a lot of hope thanks bro
I want to shout it out loud:
THIS IS ABSOLUTE AWESOME!!!
Thank you!
Very helpful, thank you!
This is an excellent channel
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?
Amazing videos!
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!
Thank you very much
Great ...
Thanks , its great
Very nice
OMG! sheets are amazing now!
Your words and actions are very clear to understand 100%
Thanks!
awesome!
Nice video.
Simply!!! Wow.
Thanks you so much Sir
👍
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 ?
Thanks for your job. Is it possible to use UDFs in ARRAYFORMULA? I couldn't manage to do that.
Thanks a lot for taking the time to share this with us.
:)
The Best Vídeos. Ótimo ensinamento, melhor didática Ever
I loved too. Dorei tb.
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.
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.
Amazing
Great tutorial 👍
:)
Very Nice😀🙃😉
Gratidão
two thumbs up
1:00 Documentation says that in countries that uses coma as currency separator (my case), substitute comma by inverted slash. Example: ={1\2\3}
wow all I can say is... thanks!
Hey can you do it for Google assistant remember commands pass from Google sheets ,you can do one tutorial.
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!
HI! How to summarize values in rows if in the table of 400 columns by means of ARRAYFORMULA?
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
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.
What about 2-dimensional tables? Like for example, what if I want to add together column and row in an NxN matrix?
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)))
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.
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}
🙏🏻
xlookup allows you to lookup a values to the left without resorting to an array.
Hi, this is great! What if we have more questions around this? How can we contact you?
make series on finance formula please.
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?
Is it possible to use an ArrayFormula with the Googlefinance function? 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 ua-cam.com/video/ljvB2arCsNQ/v-deo.html
I wish you guys would make simpler videos for intermediate users
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
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
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)
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.
At 6:30 how did you lock the cells so quickly?
Thanks, great video!
F4
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 gorge pic
Arrayformula apply to number to text format convert in auto mode conversion