SOLVED
Home

Creating a number range omitting certain numbers

Highlighted
pete atkinson
New Contributor

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 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
Solution

Re: Creating a number range omitting certain numbers

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

Re: Creating a number range omitting certain numbers

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

Re: Creating a number range omitting certain numbers

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

Re: Creating a number range omitting certain numbers

That has worked thank you so much for your help

Re: Creating a number range omitting certain numbers

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)

 

Re: Creating a number range omitting certain numbers

When for the collection

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

if A1=1

 

Related Conversations
Uploading MP3 and WAV Only files
Gordon Charles in Microsoft Stream Forum on
2 Replies
Create folder structures in FILES
Rene Waldboth in Yammer on
2 Replies
Unable to Publish
Kevin Malley in Microsoft Stream Forum on
5 Replies
Arrange Videos in a Playlist
anonymous-user in Microsoft Stream Forum on
7 Replies
Unable to upload videos
Joe Moretti in Archived on
5 Replies
Searchable video content
anonymous-user in Microsoft Stream Forum on
2 Replies