Mar 06 2018 04:04 AM
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
Mar 06 2018 05:21 AM
SolutionHi 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
Mar 06 2018 05:44 AM
Mar 06 2018 05:54 AM
Mar 06 2018 05:57 AM
Mar 06 2018 08:10 AM
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)
Mar 06 2018 08:33 AM
When for the collection
=WORKDAY.INTL(A1,1,"0000010")
if A1=1
Mar 06 2018 05:21 AM
SolutionHi 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