Google Sheets ARRAYFORMULA, Introductions to Arrays, ARRAY_CONSTRAIN, SORT Functions Tutorial
HTML-код
- Опубликовано: 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.
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!
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!
Your videos are all great, and this one is even better than most!
the best video I found about arrays! finally I understood! congrats!
This is great. I love how you break it all down into its steps first. Very helpful. Love it
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 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.
Straight to the point and useful! Feel happy about thumbing up!
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!
The best videos I found on google sheets
This is the best channel on google sheets for sure
Amazing tuts! It's working like magic! 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 🙏🏽🙌🏽
Very useful and clear tutorial. Thank you so much!
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.
Hi , thank you so much for this great tutorial !!
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.
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! Very Helpful!
Excellent training video. Thank you!
Salutations to probably the best teacher in the world! And trust me this is not exaggeration!
VOCÊS SÃO PERFEITOS!
Great video!! Thanks a lot!
Thanks for your video Extremely useful!
This is really helpful. Well explained. Thank you!
THANK YOU SO MUCH!!!
I've tried to solve it for three days!
Thanks for sharing. one of the best video.
Very useful. Thank you so much!
Amazing channel. Amazing author. Great explanation
Thank you so much for the video! It is very useful on introducing array and array formula.
Very nice explanation of array formula.
Much needed video bro. U r amazing
Thanks for the video ... just great
Thank you for this tutorial. I've been learning a lot from your channel. Thank you so much.
Happy to hear that!
amazing! thank you so much. this will help me in my reports.
I’m mind blown! I’ve moved around columns for years!
The best channel that makes it very easy to grasp that complex looking stuff. Thanks alot. May I know which mic you are using 😊
Mindblowing illustration.
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 helpful, thank you!
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
This is an excellent channel
The best of the best you give a lot of hope thanks bro
Amazing videos!
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?
Super awsome Bro..
I want to shout it out loud:
THIS IS ABSOLUTE AWESOME!!!
Thank you!
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?
The Best Vídeos. Ótimo ensinamento, melhor didática Ever
I loved too. Dorei tb.
Thanks , its great
OMG! sheets are amazing now!
Thanks a lot for taking the time to share this with us.
:)
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.
Thank you very much
Your words and actions are very clear to understand 100%
Thanks!
Simply!!! Wow.
Thanks you so much Sir
👍
Nice video.
Thanks for your job. Is it possible to use UDFs in ARRAYFORMULA? I couldn't manage to do that.
Great ...
awesome!
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
Very Nice😀🙃😉
HI! How to summarize values in rows if in the table of 400 columns by means of ARRAYFORMULA?
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!
Amazing
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 ?
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.
Great tutorial 👍
:)
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.
Very nice
Hey can you do it for Google assistant remember commands pass from Google sheets ,you can do one tutorial.
wow all I can say is... thanks!
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.
two thumbs up
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)))
1:00 Documentation says that in countries that uses coma as currency separator (my case), substitute comma by inverted slash. Example: ={1\2\3}
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
Gratidão
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, 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
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}
What about 2-dimensional tables? Like for example, what if I want to add together column and row in an NxN matrix?
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 ruclips.net/video/ljvB2arCsNQ/видео.html
Is it possible to use an ArrayFormula with the Googlefinance function? Thanks
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.
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?
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!
🙏🏻
Hi, this is great! What if we have more questions around this? How can we contact you?
xlookup allows you to lookup a values to the left without resorting to an array.
I wish you guys would make simpler videos for intermediate users
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?
ruclips.net/video/NM5-bAUP29U/видео.html
make series on finance formula please.
For some reason my google sheets do not except coma for separating columns but requires back slash \
that's ok, in Europe they use different separators, the logic is the same.
thanx
You can specify your Google Sheets location under the File menu:
File > Spreadsheet settings.
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?
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}
Does ArrayFormula work with countif?
Depends on what you mean. You can put array as a range I believe.