Number intervals - positive to negative

[froggeja]

I have six columns and 15,625 rows. My data is working with combinations of +2 to -2.

In column A, I want to sequence my input data as +2,+1,0,-1,-2 and then repeat until row 15,625

In column B, I want to sequence it as five +2s, then five +1s, then five 0s, then five -1s, then five -2s.

In column C, I want to sequence it as 25 +2s, then 25 +1s, then 25 0s, then 25 -1s, then 25 -2s.

In column D, I want to sequence it as 125 +2s, then 125 +1s, then 125 0s, then 125 -1s, then 125 -2s.

In column E, I want to sequence it as 625 +2s, then 625 +1s, then 625 0s, then 625 -1s, then 625 -2s.

In column F, I want to sequence it as 3,125 +2s, then 3,125 +1s, then 3,125 0s, then 3,125 -1s, then 3,125 -2s.

This will create a permutations matrix of 15,625 outcomes of six questions with 5 answers each.

I think I need to modify these formulas:
=MOD(ROWS($A$1:$A1)-1,5)+1
=MOD((INT((ROWS($A$1:$A1)-1)/5)+1)-1,5)+1
=MOD((INT((ROWS($A$1:$A1)-1)/25)+1)-1,5)+1
=MOD((INT((ROWS($A$1:$A1)-1)/125)+1)-1,5)+1
=MOD((INT((ROWS($A$1:$A1)-1)/625)+1)-1,5)+1
=MOD((INT((ROWS($A$1:$A1)-1)/3125)+1)-1,5)+1


Hope you can help!

 

[RonB1111]

Try wrapping each of your formulas within a LOOKUP() as follows:
=LOOKUP(YourFormula,{1,2,3,4,5},{-2,-1,0,1,2})

 

[froggeja]

Ron, you are a genius.

thank you enormously.

 

[RonB1111]

You're welcome!

 

[shg]

An alternative: In A1 and copy down and right,

=MOD(INT(5 * (ROWS($A$1:A1) - 1) / 5 ^ COLUMNS($A$1:A1)), 5) - 2

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][/tr][tr][td]

1​

[/td][td]

-2​

[/td][td]

-2​

[/td][td]

-2​

[/td][td]

-2​

[/td][td]

-2​

[/td][td]

-2​

[/td][/tr]
[tr][td]

2​

[/td][td]

-1​

[/td][td]

-2​

[/td][td]

-2​

[/td][td]

-2​

[/td][td]

-2​

[/td][td]

-2​

[/td][/tr]
[tr][td]

3​

[/td][td]

0​

[/td][td]

-2​

[/td][td]

-2​

[/td][td]

-2​

[/td][td]

-2​

[/td][td]

-2​

[/td][/tr]
[tr][td]

4​

[/td][td]

1​

[/td][td]

-2​

[/td][td]

-2​

[/td][td]

-2​

[/td][td]

-2​

[/td][td]

-2​

[/td][/tr]
[tr][td]

5​

[/td][td]

2​

[/td][td]

-2​

[/td][td]

-2​

[/td][td]

-2​

[/td][td]

-2​

[/td][td]

-2​

[/td][/tr]
[tr][td]

6​

[/td][td]

-2​

[/td][td]

-1​

[/td][td]

-2​

[/td][td]

-2​

[/td][td]

-2​

[/td][td]

-2​

[/td][/tr]
[tr][td]

7​

[/td][td]

-1​

[/td][td]

-1​

[/td][td]

-2​

[/td][td]

-2​

[/td][td]

-2​

[/td][td]

-2​

[/td][/tr]
[tr][td]

8​

[/td][td]

0​

[/td][td]

-1​

[/td][td]

-2​

[/td][td]

-2​

[/td][td]

-2​

[/td][td]

-2​

[/td][/tr]
[tr][td]

9​

[/td][td]

1​

[/td][td]

-1​

[/td][td]

-2​

[/td][td]

-2​

[/td][td]

-2​

[/td][td]

-2​

[/td][/tr]
[tr][td]

10​

[/td][td]

2​

[/td][td]

-1​

[/td][td]

-2​

[/td][td]

-2​

[/td][td]

-2​

[/td][td]

-2​

[/td][/tr]
[tr][td]

11​

[/td][td]

-2​

[/td][td]

0​

[/td][td]

-2​

[/td][td]

-2​

[/td][td]

-2​

[/td][td]

-2​

[/td][/tr]
[tr][td]

12​

[/td][td]

-1​

[/td][td]

0​

[/td][td]

-2​

[/td][td]

-2​

[/td][td]

-2​

[/td][td]

-2​

[/td][/tr]
[/table]

 

[froggeja]

Wow - that's superb. This works too and deals with the issue of my original hardcoded formula. Thanks.

 

[froggeja]

Is it possible to modify the formula (=MOD(INT(5 * (ROWS($A$1:A1) - 1) / 5 ^ COLUMNS($A$1:A1)), 5) - 2) so the data in column A, for instance, ranks:
2
1
0
-1
-2
2
1
0
-1
-2
etc,.

thanks

 

[RonB1111]

Reverse the =MOD()-2 like this:
=2-MOD(INT(5 * (ROWS($A$1:A1) - 1) / 5 ^ COLUMNS($A$1:A1)), 5)

Alternatively, put a minus sign in front, like this:
= -(MOD(INT(5 * (ROWS($A$1:A1) - 1) / 5 ^ COLUMNS($A$1:A1)), 5) - 2)

 

[froggeja]

Ron - thanks again. I'm extremely appreciative.

J