Forum Discussion

Copper Contributor

Mar 06, 2018

Solved

Creating a number range omitting certain numbers

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 w...

excel

Tinn Keeper
Mar 06, 2018
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
Number range.xlsx45 KB

Pete_Atkinson

Copper Contributor

Mar 06, 2018

Thanks for the quick reply.
I have tried the formula you provided but i keep getting error messages

Tinn Keeper

Brass Contributor

Mar 06, 2018

It is possible that you should replace the ";" by ","

Pete_Atkinson
Copper Contributor
Mar 06, 2018
That has worked thank you so much for your help
- Willy Lau
  Iron Contributor
  Mar 06, 2018
  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)
  - SergeiBaklan
    Diamond Contributor
    Mar 06, 2018
    When for the collection
    
    =WORKDAY.INTL(A1,1,"0000010")
    
    if A1=1