PDA

View Full Version : Extract missing digits



YasserKhalil
06-08-2012, 04:51 PM
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

mikerickson
06-09-2012, 01:09 AM
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)