Hello everyone
I have a range of numbers say (1 to 15) in column A
(1,2,3,5,6,7,9,10,12,13,14,15)
There are missing digits (4,8,11)
I want a formula that can extract these missing digits
Hello everyone
I have a range of numbers say (1 to 15) in column A
(1,2,3,5,6,7,9,10,12,13,14,15)
There are missing digits (4,8,11)
I want a formula that can extract these missing digits
Define a name
Name: MinToMax
RefersTo: =ROW(INDEX(Sheet1!$A:$A, MIN(Sheet1!$A:$A), 1): INDEX(Sheet1!$A:$A, MAX(Sheet1!$A:$A), 1))
Then put this CSE formula and drag down
=IFERROR(SMALL(IF(COUNTIF($A$1:$A$100,MinToMax)=0, MinToMax), ROW(A1)), "")
This formula should be entered with Ctrl-Shift-Enter (Cmd+Return for Mac)