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

Tinn Keeper

Brass Contributor

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.

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)