Hi Mark. Nice examples! Great intro to using the power of SEQUENCE in combination with other dynamic array functions. Thanks for sharing :)) Thumbs up!!
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.
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.
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
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
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.
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.
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?
@@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!