The gist for this lambda function can be found here.
The goal
Sometimes we may want to create a simple list of integers from some starting value to some ending value.
This is easy enough with the SEQUENCE function. For example, suppose we want to create a list of integers from 1 to 10.
This is really all it takes:
=SEQUENCE(10)
The first parameter is the number of rows we want in the sequence. The remaining parameters default to 1, so the formula above is equivalent to this:
=SEQUENCE(10, 1, 1, 1)
Above, the second parameter is the number of columns we want, the third parameter is the number to start from and the fourth parameter is the difference between the values in each successive row.
Ok, that’s easy. But what if we want a list of numbers from 15 to 25? This is what we would need:
=SEQUENCE(11, 1, 15, 1)
The number of rows is 11 because a list of integers from 15 to 25 includes both endpoints and so is 11 rows.
That’s fine, but is probably better expressed this way:
=SEQUENCE(25 - 15 + 1, 1, 15, 1)
And what if we want it to be a list of every 2nd integer between those endpoints? Or every third integer? Or what if we need a descending list of integers? We’d certainly have to change the calculation of the first and the last argument. So, with all of that said and seeing how things could get a little complicated, the goal for this post is:
Create a simple lambda function that will create a list of integers between arbitrary endpoints, allowing for successive list items to be an arbitrary distance apart
A solution
Here’s a lambda function I’ve called L:
/*
from is the first integer in the list
to is the target integer in the list
step is the difference between successive integers
*/
L =LAMBDA(from,to,[step],
LET(
_step, IF(ISOMITTED(step), IF(from > to, -1, 1), step),
//arguments should be single integers
_check, LAMBDA(x, OR(ROWS(x) + COLUMNS(x) <> 2, INT(x) <> x)),
IF(
//if any of these are TRUE, then there's an array somewhere
//array = no bueno
OR(_check(from), _check(to), _check(_step)),
#VALUE!,
LET(
_diff, ABS(to - from),
_rows, ROUNDUP((_diff + 1) / ABS(_step), 0),
SEQUENCE( _rows, 1, from, _step)
)
)
)
);
This function takes two required parameters and one [optional] parameter:
- from – the first integer in the list of integers
- to – the target integer in the list of integers
- [step] – the distance that each successive integer should be from each other
This is how it works:
As you can see, by changing from SEQUENCE(rows, columns, start, step) to L(from, to, step), we can simplify this simple task at the (deliberate) expense of some flexibility.
You’re welcome to take the definition of the function and use it in your projects if you think it will be useful. If you’d like to understand how the function works, please read on.
How it works
As a reminder:
/*
from is the first integer in the list
to is the target integer in the list
step is the difference between successive integers
*/
L =LAMBDA(from,to,[step],
LET(
_step, IF(ISOMITTED(step), IF(from > to, -1, 1), step),
//arguments should be single integers
_check, LAMBDA(x, OR(ROWS(x) + COLUMNS(x) <> 2, INT(x) <> x)),
IF(
//if any of these are TRUE, then there's an array somewhere
//array = no bueno
OR(_check(from), _check(to), _check(_step)),
#VALUE!,
LET(
_diff, ABS(to - from),
_rows, ROUNDUP((_diff + 1) / ABS(_step), 0),
SEQUENCE( _rows, 1, from, _step)
)
)
)
);
The function begins with LET, defining:
- _step – which handles the optional step parameter. If step is omitted, then we provide a default. The default is -1 if the from parameter is greater than the to parameter (i.e. the list is going to descend), otherwise it is 1 (the list will ascend). If step is not omitted, then its value is assigned to _step.
- _check – here we use an embedded lambda function to check two conditions:
- ROWS(x) + COLUMNS(x) <> 2, which is equivalent to asking: “Does x have more than one row or more than one column?”, and
- INT(x) <> x, which is equivalent to asking: “When we convert x to an integer, is it now different to x?”
- If either of those conditions are true, then the function returns TRUE. So, we can pass the three parameters into this function and, if the function returns TRUE for any one of them, we can determine that something is not right and we can exit L with a #VALUE! error, which is exactly what happens next.
The return value of this first LET expression is decided by the IF( on line 12 of the code block above. We check each of the parameters using the function _check. If any of them are TRUE, then the return value of L is #VALUE!
If they are all FALSE, then the OR( is FALSE and we’ve determined that each parameter is a single integer.
We continue in the “else” part of the IF expression with another LET, defining:
- _diff – which calculates the absolute difference between to and from
- _rows – where we calculate the number of rows we need in the output array. _diff + 1 , to account for the inclusion of endpoints, divided by ABS(_step) to adjust the total number of rows according to the magnitude of the distance between successive integers. All of this wrapped with ROUNDUP, because we can’t pass a decimal to the rows parameter of SEQUENCE.
- The return value of this inner LET expression is then the call to SEQUENCE as described at the beginning of the post.
In summary
We saw that we use SEQUENCE to create a list of integers in Excel.
We saw that sequences that don’t start at 1, or descending sequences, or sequences with a step value of something other than 1 can become a little tricky to get right.
We saw how to use LAMBDA to create a list of integers between arbitrary endpoints.
sandeep kothari says:
Wonderfully explained.
OP says:
Thank you!
Rathinagiri Subbiah says:
Simple but so powerful! Thank you!