MOD Func

[ExcelChampion]

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
	A	B	C	D
1	3
2
3	1
Sheet1

 

[ravishankar]

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]

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]

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]

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]

Todd

Can you explain the logic behind this?

 

[ExcelChampion]

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.

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]

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

 

[ExcelChampion]

Now why couldn't I have thought of that? Grr...

Thanks, Greg!

 

[Norie]

Todd

Couldn't you use something like this?

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