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

Row\Col	A​	B​	C​	D​	E​	F​
1​	-2​	-2​	-2​	-2​	-2​	-2​
2​	-1​	-2​	-2​	-2​	-2​	-2​
3​	0​	-2​	-2​	-2​	-2​	-2​
4​	1​	-2​	-2​	-2​	-2​	-2​
5​	2​	-2​	-2​	-2​	-2​	-2​
6​	-2​	-1​	-2​	-2​	-2​	-2​
7​	-1​	-1​	-2​	-2​	-2​	-2​
8​	0​	-1​	-2​	-2​	-2​	-2​
9​	1​	-1​	-2​	-2​	-2​	-2​
10​	2​	-1​	-2​	-2​	-2​	-2​
11​	-2​	0​	-2​	-2​	-2​	-2​
12​	-1​	0​	-2​	-2​	-2​	-2​

 

[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