SOLVED

Creating a number range omitting certain numbers

Copper Contributor

Hi

I am trying to create a number range that will omit every 7th number i.e 1,2,3,4,5,6,8,9,10,11,12,13,15,16,17,18,19,20.

I am doing this to create a number range in excess of 14000 numbers so would like an automated way to create this rather than having to manually remove every 7th number from the range.

Any help would be greatly appreciated

6 Replies
best response confirmed by Pete_Atkinson (Copper Contributor)
Solution

Hi Pete,

If you fill in the first number, you could use =IF(MOD(A1+1;7)=0;A1+2;A1+1) as from the second number to exclude the multiples of 7.

Enclosed an example.

Kr

TK

Thanks for the quick reply.
I have tried the formula you provided but i keep getting error messages
It is possible that you should replace the ";" by ","
That has worked thank you so much for your help

Hi, @Pete_Atkinson.  @Tinn Keeper's solution is perfect which is quick and easy. 

 

Just for discussion only, if you need an answer without checking on another cell's value, you may try 

=ROW()+INT((ROW()-1)/6)

 

When for the collection

=WORKDAY.INTL(A1,1,"0000010")

if A1=1

 

1 best response

Accepted Solutions
best response confirmed by Pete_Atkinson (Copper Contributor)
Solution

Hi Pete,

If you fill in the first number, you could use =IF(MOD(A1+1;7)=0;A1+2;A1+1) as from the second number to exclude the multiples of 7.

Enclosed an example.

Kr

TK

View solution in original post