# Formula help

#### c_m

##### Well-known Member
I am trying to figure out a formula so that
for variable in cell C1 = 4 (for instance)
Input Output
1 1
2 2
3 3
4 1
5 2
6 3
7 4
8 1
9 2
10 3
11 4
12 1
and so on.
if C1 = 7 then the series would be
1,2,3,4,5,6,1,2,3,4,5,6,7,1,2,3,4,5,6,7...
any ideas on what the formula should be?

Thanks

### Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

#### Oaktree

##### MrExcel MVP
This seems like a peculiar pattern to me... is it truly 1 through n-1 in the first grouping then 1 through n repeated? Or, was there a mistake where it should be 1 through n each time including the first group?

#### schielrn

##### Well-known Member
Maybe try in cell B2 (assuming 1 is in B1):

=if(B1=C1,1,B1+1)

Then copy that down. Hope that helps.

#### c_m

##### Well-known Member
This seems like a peculiar pattern to me... is it truly 1 through n-1 in the first grouping then 1 through n repeated? Or, was there a mistake where it should be 1 through n each time including the first group?

there was no mistake in the output...the first group is from 1 to n-1 and rest from 1 to n.

So all I am looking for is...if i change n the output column updates automatically

#### barry houdini

##### MrExcel MVP
Assuming A1 down has 1,2,3,etc.....try this formula in B1 copied down

=MOD(A1-(A1<=C\$1),C\$1-(A1<=C\$1))+1

#### schielrn

##### Well-known Member
I believe this will work still based on B1 having 1 and C1 containing the number you specify:

=IF(B1=\$C\$1,1,IF(AND(B1+1=\$C\$1,COUNTIF(\$B\$1:\$B1,\$C\$1-1)=1),1,B1+1))

#### c_m

##### Well-known Member
Assuming A1 down has 1,2,3,etc.....try this formula in B1 copied down

=MOD(A1-(A1<=C\$1),C\$1-(A1<=C\$1))+1

brilliant!
Works perfect.
Thankyou so much

Replies
2
Views
144
Replies
1
Views
186
Replies
13
Views
338
Replies
6
Views
274
Replies
6
Views
394

1,191,353
Messages
5,986,166
Members
440,008
Latest member
Cmbuck

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back