Today we are going to discuss about a method to find the missing numbers in a given sequence of numbers. We are going to see the importance of the construction using ROW and INDIRECT functions to form the sequence of number. At the end, will show you the trick using LET function. Lot to learn, let's dive in.
Here, we can see, sequence of numbers from 1 to 25 in the range C3:C14. But some of the numbers are missing in between. Our task is to find those missing numbers.
Formula in E3 cell is,
=SMALL(IF(ISERROR(MATCH(ROW(INDIRECT(MIN($C$3:$C$14)&":"&MAX($C$3:$C$14))),
$C$3:$C$14,0)),ROW(INDIRECT(MIN($C$3:$C$14)&":"&MAX($C$3:$C$14)))),ROWS(E$3:$E3))
This may look like a wild one, but you can see that the ROW and INDIRECT construction is used in 2 places. Let me start this from scratch, to help you understand better.
Step 1:
We are going to create new sequence of numbers without any missing numbers from the given numbers.
Starting position of new sequence -> Minimum value of given numbers,
Ending position of new sequence -> Maximum value of given numbers.
So, Minimum value of C3:C14 is 1.
Maximum value of C3:C14 is 25. This is dynamic, if the start and end position changes in given number, then this will change automatically.
We concatenate these 2 values with colon (":"), so this will become text. Then, we are going to feed this into INDIRECT function.
What INDIRECT function does?
It converts text reference into range reference.
here, text value of "1:25" is getting converted into the range reference of 1:25, that means rows 1 to 25.
Now, we are going to extract only the row numbers of this range using ROW function.
This construction delivers the sequence of numbers from 1 to 25.
Step 2:
Find the relative positions of missing numbers.
Consider the input sequence as A and the new sequence as B.
Pick 1,2 and 3 from sequence B and search those in sequence A. It is there in 1,2 and 3 position.
Pick 4, 5 from sequence B and search those in sequence A, it is not there. In the same way, we are going to pick the relative positions of each item in B. We can get help from MATCH function.
We used Sequence B in lookup_value argument,
Sequence A in lookup_array argument. In the match_type argument, we used zero for the exact match.
This function throws numbers and Not Available error values. Here, our interest is on error values and so we wrap this into ISERROR function. Then the output will look like below,
Step 3:
Extract the relative positions of missing numbers.
Take a close look at this array,
TRUE's are there in the positions of 4,5,7..., those are all missing numbers.
So, if we extract the positions of TRUE, we can find missing numbers.
Wrap this array of Boolean values into IF function, use our ROW and INDIRECT function construction in value_if_true argument, then we will get the relative positions of TRUE.
we are going to wrap this array into SMALL function and for the argument [k], use ROWS function with expandable range as shown below.
Use of LET function:
LET function helps to increase the speed, Efficiency! and to improve the readability.
This function requires excel version 2021 or above.
In our case, ROW and INDIRECT function construction is getting repeated 2 times to create sequence of numbers. When excel evaluates this formula, this sequence is calculated in each occurrence.
This will reduce the speed of calculation and will also increase the length of the formula.
Increase in length dents readability. So, to overcome this issue, we can get help from LET.
The arguments of LET function are,
=LET(name1,name_value1,calculation_or_name2,[name_value2],...)
We assigned a name (Seq) to ROW, INDIRECT function construction and we used the name in 2 places. This construction is evaluated only once and kept in memory, Then the value is used in formula whenever the name is called. You can see that; length of formula is reduced, and the readability is improved.
Commentaires