Finding Missing Number from Sequence

Hi Everyone,


I'm having some trouble understanding some code I found online that I'm trying to use. The code looks like this =SMALL(IF(COUNTIF($A$1:$A$7,ROW($1:$12))=0,ROW($1:$12),""),ROW(A1)) and is meant to find the missing numbers from a sequence of numbers. The problem i'm having is that I'd like to start the list of numbers from row 5 instead of row 1 and I can't make it work. The formula i'm struggling with is in column K on the attached spreadsheet.


Thanks for your help.

This is NOT code - it's a formula.

Explain in WORDS what you are trying to achieve. Tell us where the numbers are being drawn from and, crucially, show us what you want. Mock this up manually. Seeing a non-functioning formula that returns incorrect results without any clue about the desired outcome is pretty useless.

You can uise this array formula in K1, copied down:

=IFERROR(SMALL(IF(COUNTIF($F$5:$F$48,ROW($5:$48))=0,ROW($5:$48),""),ROWS($1:1)),"")

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

Hi Glenn, that fixed my problem. Thank you for your helpful response, wish you all the best for the new year!

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.