I have created a custom function to check if a range has sequential numbers. The range doesn’t have to be sorted.
You might need this if you are asking the user to rank certain entries and you’re expecting sequential numbers for the ranking. You can download an example file using the button on the bottom of this post.
To check if the user has entered sequential numbers, you can use this custom function.
The function returns TRUE if the range contains sequential numbers and FALSE if it doesn’t.
The range doesn’t have to be in sequential order for the function to work.
In the image below I have created a LET function in cell C2 to perform the calculation. It is explained below.
In cell C3 I have created a LAMBDA function that is used to create the custom function.
Cell C4 contains the custom function.
If you are not familiar with custom functions then this post introduces them.
LET function
Let’s review the LET function.
The LET function allows you to use variables within your formulas. This shortens formulas when you need to reuse parts of the formula. I have used Alt + Enter in the Formula Bar to insert line breaks in the formula to make it easier read and understand.
The r variable holds the range to work with. The range is sorted. The m variable holds the minimum value within the range. The s variable creates a list of sequential numbers to match against the r range.
With those three variables created, we can then perform the check.
The s<>r within the SUM function will count how many times within the range the values don’t match. Because the two ranges should be identical this SUM should equal 0. If it doesn’t it means that an entry hasn’t matched, and it will return FALSE. If all the entries match, then TRUE will be returned because the SUM will equal 0.
If an error is encountered the function will return FALSE.
LAMBDA function
The LAMBDA function accepts a range using the rng variable. The rng variable is then passed to the r variable within the LET function and it performs the calculation.
Please note: I reserve the right to delete comments that are offensive or off-topic.