Formula to show numbers except two

  • Thread starter Thread starter canvas
  • Start date Start date
C

canvas

Hi,

I have this data in column A that changes each time.
A

10

15

I have only the first 2 numbers that go from 10 to 17. I need a
formula that from A3 on throws all the numbers from 10 to 17 except
the first two. For example, in this case, I have numbers 10 and 15,
the formula should show:

A

10
15
11
12
13
14
16
17

If I had 12 and 17, it shoul show 10, 11, 13, 14, 15 and 16. Hope my
question is clear.

Thanks
 
Will the 2 numbers ever be the same:

A1 = 17
A2 = 17

Will the the 2 numbers *always* be listed in ascending order? Will this ever
be a possibility:

A1 = 15
A2 = 10
 
Hi, Valko

Thanks for your answer. No, the numbers can be unsorted. Second case
is correct. First two numbers can be 13 17 or 15 11.

Thanks in advance
 
Try this...

Create this defined name

Goto the menu Insert>Name>Define
Name: Nums
Refers to:

=ROW(INDIRECT("10:17"))

OK

Enter this array formula** in A3 and copy down to A8:

=SMALL(IF(ISNA(MATCH(Nums,A$1:A$2,0)),Nums),ROWS(A$3:A3))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
 
Back
Top