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