Help with formula please.

[MrsFrankieH]

Hello everyone,
My computer is Dell and my operating system is Windows 10.
My goal is to be able to paste numbers in cells (in this example cells D2 to H2) and have appropriate numbers populate the target cells (cells above and below colored cells). I don't know the appropriate terminology to explain so this is what the cells look like after I've used a rudimentary formula. The formula I used to populate the middle (colored) cells are D6=INT(D2/10), E6=MOD (D2,10), G6=INT(E2/10), H6=MOD(E2,10), ...etc.

On the left are the numbers used to populate the cells above and below. For example, in the first box in yellow, the number in the first yellow cell is "0", so the number above "0" would be "9" and the number below "0" would be "1". In the next yellow box over, the number is "5", so looking at the chart on the side, the number above "5" would be "6" and the number below "5" would be "4". The same process would be repeated in the the other colored cells.​

I hope I was able to explain what I need help with. I don't have the terminology to explain it like I'd like to.
Thank you so much in advance.

 

[Eric W]

Try:

D5: =MOD(D6-1, 10)

copy that to the other cells on that row.

D7: =MOD(D6+1, 10)

copy to the other cells on that row.

This just adds/subtracts one. If you want to actually use the tables on the left, we can use some lookup formulas.

 

[sheetspread]

If you want to actually use the tables on the left

Then something like:

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Up	Down
2	0	9		5	9	14	17	30
3	1	8
4	2	7		Up	Down		Up	Down		Up	Down		Up	Down		Up	Down
5	3	6		9	6		9	0		0	5		0	8		2	1
6	4	5		0	5		0	9		1	4		1	7		3	0
7	5	4		1	4		1	8		2	3		2	6		4	9
8	6	3
9	7	2
10	8	1
11	9	0
Sheet5

Cell Formulas
Range	Formula
D5	=INDEX($A$2:$A$11,IFERROR(1/(1/(MATCH(D6,$A$2:$A$11,0)-1)),10))
D6	=INT(D2/10)
D7	=INDEX($A$2:$A$11,MOD(MATCH(D6,$A$2:$A$11,0),10)+1)
E5	=INDEX($B$2:$B$11,IFERROR(1/(1/(MATCH(E6,$B$2:$B$11,0)-1)),10))
E6	=MOD(D2,10)
E7	=INDEX($B$2:$B$11,MOD(MATCH(E6,$B$2:$B$11,0),10)+1)

 

[MrsFrankieH]

Thank you Erik, also, it's great to see you again!!!

I'll try this and if it doesn't do what I need for it to do, I have other systems this would be perfect for!!! I didn't have the vocabulary for it but 'lookup formulas' would be best for this particular task.
I'll update you after I try it!

 

[MrsFrankieH]

Thank you Spreadsheet!!! I'll also try this formula!! I appreciate you guys helping me. I'll let you know how it went, Thanks again!!!

 

[MrsFrankieH]

Hello Spreadsheet!!!

D5, E5, D6, and E6 worked like a charm but D7 and E7 gave me an N/A error. I did a little cell surgery (in case the cells were dead) and still got the errors. Is there anything else I can do?

Thank you so much, I really appreciate your help!

 

[MrsFrankieH]

Hello again Eric!!! I gave the formula a go and although it's not the right kind of formula for this task, it works amazing for other tasks. Could you show me a lookup formula? I really appreciate it!! Thank you,,,,,,

 

[sheetspread]

Here are all the formulas:

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Up	Down
2	0	9		5	9	14	17	30
3	1	8
4	2	7		Up	Down		Up	Down		Up	Down		Up	Down		Up	Down
5	3	6		9	6		9	0		0	5		0	8		2	1
6	4	5		0	5		0	9		1	4		1	7		3	0
7	5	4		1	4		1	8		2	3		2	6		4	9
8	6	3
9	7	2
10	8	1
11	9	0
Sheet5

Cell Formulas
Range	Formula
D5	=INDEX($A$2:$A$11,IFERROR(1/(1/(MATCH(D6,$A$2:$A$11,0)-1)),10))
D6	=INT(D2/10)
D7	=INDEX($A$2:$A$11,MOD(MATCH(D6,$A$2:$A$11,0),10)+1)
E5	=INDEX($B$2:$B$11,IFERROR(1/(1/(MATCH(E6,$B$2:$B$11,0)-1)),10))
E6	=MOD(D2,10)
E7	=INDEX($B$2:$B$11,MOD(MATCH(E6,$B$2:$B$11,0),10)+1)
G5	=INDEX($A$2:$A$11,IFERROR(1/(1/(MATCH(G6,$A$2:$A$11,0)-1)),10))
G6	=INT(E2/10)
G7	=INDEX($A$2:$A$11,MOD(MATCH(G6,$A$2:$A$11,0),10)+1)
H5	=INDEX($B$2:$B$11,IFERROR(1/(1/(MATCH(H6,$B$2:$B$11,0)-1)),10))
H6	=MOD(E2,10)
H7	=INDEX($B$2:$B$11,MOD(MATCH(H6,$B$2:$B$11,0),10)+1)
J5	=INDEX($A$2:$A$11,IFERROR(1/(1/(MATCH(J6,$A$2:$A$11,0)-1)),10))
J6	=INT(F2/10)
J7	=INDEX($A$2:$A$11,MOD(MATCH(J6,$A$2:$A$11,0),10)+1)
K5	=INDEX($B$2:$B$11,IFERROR(1/(1/(MATCH(K6,$B$2:$B$11,0)-1)),10))
K6	=MOD(F2,10)
K7	=INDEX($B$2:$B$11,MOD(MATCH(K6,$B$2:$B$11,0),10)+1)
M5	=INDEX($A$2:$A$11,IFERROR(1/(1/(MATCH(M6,$A$2:$A$11,0)-1)),10))
M6	=INT(G2/10)
M7	=INDEX($A$2:$A$11,MOD(MATCH(M6,$A$2:$A$11,0),10)+1)
N5	=INDEX($B$2:$B$11,IFERROR(1/(1/(MATCH(N6,$B$2:$B$11,0)-1)),10))
N6	=MOD(G2,10)
N7	=INDEX($B$2:$B$11,MOD(MATCH(N6,$B$2:$B$11,0),10)+1)
P5	=INDEX($A$2:$A$11,IFERROR(1/(1/(MATCH(P6,$A$2:$A$11,0)-1)),10))
P6	=INT(H2/10)
P7	=INDEX($A$2:$A$11,MOD(MATCH(P6,$A$2:$A$11,0),10)+1)
Q5	=INDEX($B$2:$B$11,IFERROR(1/(1/(MATCH(Q6,$B$2:$B$11,0)-1)),10))
Q6	=MOD(H2,10)
Q7	=INDEX($B$2:$B$11,MOD(MATCH(Q6,$B$2:$B$11,0),10)+1)

 

[MrsFrankieH]

Thank you so much again Spreedsheet!! I'm going to try these now and keep you updated with my progress. I truly appreciate your help and hard work!!!

 

[MrsFrankieH]

Hello Spreadsheet!!
First, thank you so much for your help. I truly appreciate it from the bottom of my heart for you to take the time and skill to help me.
Unfortunately, I keep get N/A errors. I'm doing exactly what you are posting.

I don't know why it's happening. Maybe my Excel is corrupted?