# MOD Func

#### ExcelChampion

##### Well-known Member
The below is simple:

If I type 3 in A1 the formula returns 1. If I type 1 in A1 the formula returns 2. If I type 2 in A1 the formula returns 3.

I want the opposite. If I type 2 in A1 it returns 1. If I type 1 in A1 it returns 3. If I type 3 in A1 it retunrs 2.

Can't get my head around it. Any help is appreciated.
PPT.xls
ABCD
13
2
31
Sheet1

### Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

#### ravishankar

##### Well-known Member
HI
Mod returns the remainder after dividing A1 by 2nd argument - in your case 3
if A1 = 3, A3 = 3/3 + 1 = 2
if A1 = 2, A3 = 2/3 +1 = 3 (note 2 is the remainder +1 )
if A1 = 1 , A3 = 1/3 +1 = 2
I can't figure out any relation between your input and the output you expect
Ravi

#### ExcelChampion

##### Well-known Member
Ok, forget MOD. How could I, cleanly, get the desired output?

If 3 then 2
If 2 then 1
If 1 then 3

The number of arguments will be dynamic. There could be 50 arguments, so hard coding won't work.

My example is simply showing what I want only I need it in reverse. Ah, difficult to explain...

#### Norie

##### Well-known Member
Todd

If there might be 50 arguments I think you need to explain further.

For example what might 36 return?

And what about 27, if it's involved?

#### ExcelChampion

##### Well-known Member
If there are 50 arguments then 36 would return 35. 27 would retunr 26. However, 1 would return 50, and 50 would return 49.

Imagine that in the screenshot above, the numbers move forward by 1. But, when it gets to 3 it starts over. I simply want it to move backward.

Make sense yet?

#### Norie

##### Well-known Member
Todd

Can you explain the logic behind this?

#### ExcelChampion

##### Well-known Member
I should have known better to post the code from the start...

Any way, I have a user who wants to use navigation buttons in Excel like you would in PowerPoint. He may add or delete sheets. I have this code to move to the next sheet. If he gets to the last sheet the code will move him to the first sheet. All I need is a dynamic way to allow him to move to the previous sheet. If he gets to the first sheet, it will then move him to the last sheet.

Code:
``````Sub sht_Forward()
aSht = ActiveSheet.Index
Sheets(Evaluate("MOD(" & aSht & "," & Worksheets.Count & ")+1")).Select
End Sub``````

I thought the code above seemed clean and simple and thought I could do the same only in reverse.

#### Greg Truby

##### MrExcel MVP
Todd, I think you want:<ul>[*]=MOD(x-2,n)+1[/list]

#### ExcelChampion

##### Well-known Member
Now why couldn't I have thought of that? Grr...

Thanks, Greg!

#### Norie

##### Well-known Member
Todd

Couldn't you use something like this?
Code:
``````Sub sht_Forward()
If ActiveSheet.Index = Sheets.Count Then
Application.Goto Sheets(1).Range("A1"), True
Else
Application.Goto Sheets(ActiveSheet.Index+1).Range("A1"), True
End If
End Sub``````

Replies
24
Views
853
Replies
12
Views
719
Replies
3
Views
331
Replies
5
Views
312
Replies
6
Views
287

1,190,743
Messages
5,982,699
Members
439,790
Latest member
jonaust

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