SEQUENCE function in Excel | Formula for returning a sequence of numbers | Excel Off The Grid
HTML-код
- Опубликовано: 26 июл 2024
- ★ Want to automate Excel? Check out our training academy ★
exceloffthegrid.com/academy
★ Download the example file★
exceloffthegrid.com/sequence-...
★ About this video ★
The SEQUENCE function creates lists of numbers, in rows and columns, based on a start position and an interval. On its own it is a useful function, but combine it with other functions to really see its power.
In this video, we look a the new SEQUENCE function and how to master it with 5 examples.
★ Download 30 most useful Excel VBA Macros ebook for FREE ★
exceloffthegrid.com/
★ Where to find Excel Off The Grid ★
Blog: exceloffthegrid.com
Twitter: / exceloffthegrid
#MsExcel #ExcelOffTheGrid
![Young Nudy - John Wayne (feat. Metro Boomin) [Official Video]](http://i.ytimg.com/vi/NY8HnYYo2fo/mqdefault.jpg)
![MAF Teeski - Whole Thing (feat. VonOff1700) [Official Music Video]](http://i.ytimg.com/vi/SHWEj-ndz6g/mqdefault.jpg)
Thank you for the sequence function tips. Those were fun examples.
Thank You 👍
Thanks Mark. Great tip to embed SEQUENCE within TRANSPOSE to switch the default order of rows and columns 👍
I think TRANSPOSE is the easiest way to achieve this. Unless you can think of any others?
@@ExcelOffTheGrid No others I can think of so seems ideal to me.
Thanks Mark. Appreciate taking time out on a weekend to share your knowledge! Example # 4 was a good one.
Thanks Rajiv. I’m hoping to finish this series about dynamic arrays in December. Three more parts to come. 😀
@@ExcelOffTheGrid Can't wait Mark :-) Keep 'em coming!!!
Hi Mark, Happy to see you back after longtime.. 👍👍
Thanks Sri - There will be some more coming in December. 👍
Hi Mark. Nice examples! Great intro to using the power of SEQUENCE in combination with other dynamic array functions. Thanks for sharing :)) Thumbs up!!
Thank You - more dynamic array content coming soon. 😀
@@ExcelOffTheGrid Great! Eager to learn as much as possible :)) Thanks!!
Excellent💯👍👍
Thank you it was very helpful
You're welcome! 😊
Hi, is there a way to use sequence function using a button when inserting rows above? Basically what i would like to happen is that the latest entry will be inserted above from the last completed row and using a button only. So I don’t have to type the sequence from the first column. I’d appreciate to hear your thoughts.
A great "unwaffled" video, can you upload one which shows how to put a sequential number in the middle of text? for instance ABC-001-A to ABC-100-A, i could just be being thick here of course.
Thanks
Thank you 😊
It would be something like
=“ABC-”&TEXT(SEQUENCE(100),”000”)&”-A”
I’m on my phone without Excel in front of me, so I can’t check. Let me know if it works.
I was trying to use sequence to replace a simple formula like cell b4 = b3*(1+.03) for 20 rows... just trying to generate 20 years worth of simple inflation numbers. Is that possible ? thx
Yes, it's possible but you need to think of the calculation slightly differently.
Try:
=B3*((1.03)^SEQUENCE(20))
Is there a way to make this function work on Excel 2016?
Unfortunately not. It’s not even available on Excel 2019.
Is it only available in latest version ????
It’s currently only available to those with Microsoft 365. It’s not available on Excel 2019 or before.
You've never played bingo! Shocking! Pretty much every cross-generational family gathering ends up playing bingo at some point. Good fun. Nice video though.
Thanks Ross :-)
My family always ended up playing Black Jack. My grandad always won - I am convinced he was a hustler back in the day.
Sir, thanks. Does this not work with "mobile wps". Please clarify.
Sorry, I don’t know mobile wps is.
@@ExcelOffTheGrid no matter. thanks.
How do you set STEP if SEQUENCE to generate months that are n months apart? Example, list of 5 dates that are 3 months apart from 2/24/2024 is: 5/24/2024, 8/24/2024, 11/24/2024, 2/24/2025, and 5/24/2024.
=EDATE(DATE(2024,2,24),SEQUENCE(5,1,0,3))
i cant find fx sequence in my excel
It’s in Excel 2021, Excel 365, and Excel Online only. Which version have you got?
Hello sir,,, i have a question,,, im using Microsoft Office 2016 but when i type Sequence in excel,,, there's no sequence display in my cell,,, can u help my solve my problem thank you
SEQUENCE is only available in Excel 355 and Excel 2021
oh i see,,, thank u for the info
How can I get series from i.e. 44NA001 to 44NA050..
This should work:
="44NA"&TEXT(SEQUENCE(50),"000")
@@ExcelOffTheGrid How to add transpose formula to this.
@@mrjenk0073 To add transpose just make it the following:
=TRANSPOSE("44NA"&TEXT(SEQUENCE(50),"000"))
There is no sequence function in my excel what should I do any suggestions
What version of Excel do you have?
@@ExcelOffTheGrid i am using excel 2019
@@priyanshshukla4614 It’s only available for
Excel 365, and should be in Office 2021.
Re the SEQUENCE function: please open a new sheet, create a vertical 1 x 10 Excel table (with header) with arbitrary numbers in cells A1 through A10. Then enter "=SEQUENCE(9)" in cell B2 and hit return. Results in a complete mess in my case. Instead of just executing the SEQUENCE function, it extends the table in A1:A10 to the right and populates it with #SPILL! errors. Does anybody else observe that, too? Is this a known issue?
Dynamic Arrays are not compatible with Excel Tables. That is a known “feature”.
@@ExcelOffTheGrid What you mean by "compatible"? As far as I am concerned, I just want a vertical sequence next to a vertical table, but I don't want them to have anything to do with one another, other than their vicinity. Neither does the table refer into the dynamic array, nor does the array refer into the table, nor am I trying to format a dynamic array as a table. The non-compatibility you're mentioning, is that documented somewhere? Google didn't find anything...
Tables have auto expansion when values are placed directly the the right or below them. By adding the cells in B2 you a triggering the auto expansion and including those values in the Table.
Tables also have calculated columns, which repeat the same calculation for every row of that Table. Dynamic Array functions create a spill range if there are multiple cell, therefore each spill range is blocked by the Table calculated column.
This is all known behaviour, which is documented here:
support.microsoft.com/en-us/office/dynamic-array-formulas-and-spilled-array-behavior-205c6b06-03ba-4151-89a1-87a7eb36e531
Why not just resize the Table back to A1:A10 and it will all work as you expect.
@@ExcelOffTheGrid Thanks for your explanation. Yeah I know about the spilling concept, but that's not the problem here. The problem is the auto-expansion feature of the table. So, why not resize the table? Well, because each time I recalculate the SEQUENCE, I will have the same problem again. In the meantime, however, I found a workaround: if I enter some arbitrary text in the cell right next to the table header (cell B1 in my example, i.e. the cell which is just above the SEQUENCE command), then the table will not automatically expand to the right and everything works as desired. Thanks again!
Gotta get out here, they never see ,we've been here before!'"?"'
PLEASE IMPROVE THE QUALITY OF YOUR SHARING SCREEN SO WE CAN SEE WTS GOING ON
Thanks for the feedback - can you give any more specifics to help with future videos.
What device / screen size are you viewing on?