Hi Gasper. Thanks for demonstrating a creative use of the SEQUENCE function. It sure is versitile and powerful. Great example! Thumbs up!! PS - Once VSTACK is released, you will be able to solve this with: =VSTACK(SEQUENCE(10),{"";""},SEQUENCE(10,,11))
Hello, Gasper. Thanks for the explanation. Another case is that I have hundreds of names that are repeated and ordered alphabetically. I'd like to assign a serial number ranging from "01" to "n" to each name, with the following name starting from "01" again, and so on for the remaining names. Is it possible to do this with Excel? I'm doing it manually; please let me know if there's an easier approach.
Hey Gasper, I wonder if you can elaborate on this use of sequence, I need to have a sequence of numbers that have a break in them at random intervals, so I cannot use the IF
Loved it Mate! This a great dynamics way of building a Fibonacci sequence, as a helper row, which is great for a multitude of reason. Not even mentioning the charting potential for it in excel!!! PS: I am mostly only interested in .5 / .618 / .702 this right there solved that issue of mine.
This is definitely a fun one to solve. The great thing is, is with the new functions you can make it generic for any number of outputs. Immediate idea that came to me was for creating headers in a forecast file. Something like this: =LET( _periods, SEQUENCE(24), _gapAfter, 12, _gapSize, 2, _numGaps, CEILING.MATH( COUNT(_periods) / _gapAfter, 1), _newCols, SEQUENCE(COUNT( _periods ) + ( _numGaps * _gapSize)), _sequence, MOD( _newCols - 1, _gapAfter + _gapSize) + 1, _output, IFS( _sequence = _gapAfter + 1, "Total", _sequence > _gapAfter + 1, "", TRUE, INDEX(_periods, _sequence + (TRUNC(_newCols/( _gapAfter + _gapSize)) * _gapAfter))), TRANSPOSE(_output) )
I created an automatic calendar using the sequence but I wanted to add 4 blank lines under each week. I can't seem to figure out how or where to add your formula to the other formula. Is it possible?
Hi I have a question I hope you can help me Thanks in advance: My question is how to automatically number rows in sequence but per classification within the same sheet? Example: White has 10 rows, Blue has 7 rows and Red has 5 rows.
An interesting use of the new functions =LET and =IFS; I love these new functions especially LET and LAMBDA along with it's helper functions. The AFE has only made them even better :)
@@ExcelOlympics I completely agree. It is nice that Microsoft finally seems to have realised that they have a good product that can be made fantastic if they keep innovating. They have definitely started taking steps in the right direction, I can't wait to see what else is in the pipeline.
Hi, this was such a great video, thank you so much. I’m wonder if it’s possible to have a sequence repeat a certain number of times. For example, have every month starting at a date and ending at the current month ?
Thank you for your prompt reply, I think I should involve VBA in the process since i need to repeat the operation a number of times based on some value.
Hi Gasper, Thanks for this video and I thought of asking, Is there any other usage of "^" (Caret) operator except for getting the Power in Excel? Thank you.
Hi Gasper. Thanks for demonstrating a creative use of the SEQUENCE function. It sure is versitile and powerful. Great example! Thumbs up!! PS - Once VSTACK is released, you will be able to solve this with: =VSTACK(SEQUENCE(10),{"";""},SEQUENCE(10,,11))
Thanks Wayne. And yes, the new package will be a game changer...
Interesting- a solution to a problem I have thankfully yet to encounter.
You never know when the "I think I've seen this before" train will arrive whilst working with Excel :)
Hello, Gasper. Thanks for the explanation. Another case is that I have hundreds of names that are repeated and ordered alphabetically. I'd like to assign a serial number ranging from "01" to "n" to each name, with the following name starting from "01" again, and so on for the remaining names. Is it possible to do this with Excel? I'm doing it manually; please let me know if there's an easier approach.
Hey Gasper, I wonder if you can elaborate on this use of sequence, I need to have a sequence of numbers that have a break in them at random intervals, so I cannot use the IF
Loved it Mate! This a great dynamics way of building a Fibonacci sequence, as a helper row, which is great for a multitude of reason. Not even mentioning the charting potential for it in excel!!!
PS: I am mostly only interested in .5 / .618 / .702 this right there solved that issue of mine.
Brilliant stuff there b0cipapa 😀
This is definitely a fun one to solve. The great thing is, is with the new functions you can make it generic for any number of outputs. Immediate idea that came to me was for creating headers in a forecast file. Something like this:
=LET(
_periods, SEQUENCE(24),
_gapAfter, 12,
_gapSize, 2,
_numGaps, CEILING.MATH( COUNT(_periods) / _gapAfter, 1),
_newCols, SEQUENCE(COUNT( _periods ) + ( _numGaps * _gapSize)),
_sequence, MOD( _newCols - 1, _gapAfter + _gapSize) + 1,
_output, IFS( _sequence = _gapAfter + 1, "Total",
_sequence > _gapAfter + 1, "",
TRUE, INDEX(_periods, _sequence + (TRUNC(_newCols/( _gapAfter + _gapSize)) * _gapAfter))),
TRANSPOSE(_output)
)
Love that Chris. It's brilliant!
I created an automatic calendar using the sequence but I wanted to add 4 blank lines under each week. I can't seem to figure out how or where to add your formula to the other formula. Is it possible?
Hi I have a question I hope you can help me Thanks in advance: My question is how to automatically number rows in sequence but per classification within the same sheet? Example: White has 10 rows, Blue has 7 rows and Red has 5 rows.
An interesting use of the new functions =LET and =IFS; I love these new functions especially LET and LAMBDA along with it's helper functions. The AFE has only made them even better :)
True Roy. Excel is getting better and better as Microsoft is doing such a great job to improve it week after week.
@@ExcelOlympics I completely agree. It is nice that Microsoft finally seems to have realised that they have a good product that can be made fantastic if they keep innovating. They have definitely started taking steps in the right direction, I can't wait to see what else is in the pipeline.
Hi, this was such a great video, thank you so much. I’m wonder if it’s possible to have a sequence repeat a certain number of times. For example, have every month starting at a date and ending at the current month ?
Sure thing but you can only go fill series and select months and fill in Stop value. You dont even need the Sequesce function for that.
Thank you for your prompt reply, I think I should involve VBA in the process since i need to repeat the operation a number of times based on some value.
Hi Gasper, Thanks for this video and I thought of asking,
Is there any other usage of "^" (Caret) operator except for getting the Power in Excel?
Thank you.
Hi Sanoj. Thank you for that question. I have to be honest with you. I know of no other use for that symbol in Excel.
@@ExcelOlympics Thank you Casper..👏
Hey Gasper! When can we expect a new video? I am missing seeing how you work in excel, power query, PBI, etc!!
Hi Irene. Thanks for noticing. I had a few issues but will return in August.
@@ExcelOlympics - Thanks for the update - hoping all is well! Looking forward to your return👍
Hello Gasper,
Seems no videos are posted after this. hope you are doing well, expecting a comeback soon👍
Thanks for noticing Sanoj. There will be more to come :)
how i can extend to a thousand