Mirror Numbers

[Mypaperdue]

Good Morning/afternoon,

What I am trying to accomplish here is rather simple but for some odd reason, I am having some difficulty with it. I have number 1-10 listed in cell A and number 11-20 listed in cell B. Now in cell D I have number 1-20 listed randomly in no particular order. Pretty much from this point what I want in cell H is "=IF(D1="9","19", IF(D2="1","11")) I want it to shoe the mirror Etc The formula work perfectly but I have to put that formula in the cell so many times to show the numbers come out vise versa as well. My question is, How can I shorten this formula.

Also I seem to ran into some problem when the numbers are fairly large. for instance if I have a three digit number listed in cell D such as 315, I would want CELL H J I to have "=IF(LEFT(D1="3","13")) but i am getting a error message with that formula and I don't know what I am doing wrong. Thanks in advance for all the help...

	A	B	C	D	E	F	G	H	I	J
1	1	11		9		19
2	2	12		1		11
3	3	13		17		7
4	4	14		5		15
5	5	15		13
6	6	16		19
7	7	17		1
8	8	18		3
9	9	19		7
10	10	20		286				12	18	16
11				315				13	11	15

<tbody>
</tbody>

 

[Rick Rothstein]

For 1-digit numbers, the lookup is in Column A and for 2-digit numbers, the lookup is in Column B except for 10 which is looked up in Column A, correct?

Why isn't 2-digit numbers looked up in the same way as 3-digit numbers where each digit of the 3-digit number is looked up individually in Column A?

If you have a 3-digit number with a 0 in it (like 305), what number should be returned for it?

 

[Logit]

.
Just based on what you have presented and asked :

Paste in F1

=IF(LEN(D1)<3,CONCATENATE("1",D1),"")

Paste in H1

=IF(LEN(D10)=3,CONCATENATE("1",LEFT(D10,1)),"")

Paste in I1

=IF(LEN(D10)=3,CONCATENATE("1",MID(D10,2,1)),"")

Paste in J1

=IF(LEN(D10)=3,CONCATENATE("1",RIGHT(D10,1)),"")

Then drag down all four as far as required.


There is probably a 'cleaner' way of accomplishing the goal but .... ??

 

[Mypaperdue]

Okay maybe I wasn't clear at all in the last post and it didn't make any sense what so ever and for that I am sorry. Let me see if I can be a little bit more clearer. Cell N1:O6 mirrors one another. Meaning N1=O1 and O1=N1. So in cell A, whatever number is in there will reflect the opposite. For example...Cell A1 shows 395, when I glimpse over to cell N1:O6 I see N3=3 which is the 3 in "395" and the mirror of N3 is O3 and O3=8 so in cell C1 8 is shown for the first number. Lets continue with A1, the second number is 9 and when I look at N1:O6, 9 is O5 and the mirror is N5 which is 4 so in cell C1 4 is showing for the next number. So on and so on with the following number. So after all is compensate for in A1, cell C1 should have 8410. Does this make any sense to anyone?

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	395		8410											1	6
2	682		137											2	7
3	983		438											3	8
5	123		678											4	9
6	452		9107											5	10
7	481		936
8	184
9	692
10	973

<tbody>
</tbody>

 

[Marcelo Branco]

Maybe this

C1 copied down
=--(MOD(LEFT(A1)+4,10)+1&MOD(MID(A1,2,1)+4,10)+1&MOD(RIGHT(A1)+4,10)+1)

M.

 

[Rick Rothstein]

Okay maybe I wasn't clear at all in the last post and it didn't make any sense what so ever and for that I am sorry. Let me see if I can be a little bit more clearer. Cell N1:O6 mirrors one another. Meaning N1=O1 and O1=N1. So in cell A, whatever number is in there will reflect the opposite. For example...Cell A1 shows 395, when I glimpse over to cell N1:O6 I see N3=3 which is the 3 in "395" and the mirror of N3 is O3 and O3=8 so in cell C1 8 is shown for the first number. Lets continue with A1, the second number is 9 and when I look at N1:O6, 9 is O5 and the mirror is N5 which is 4 so in cell C1 4 is showing for the next number. So on and so on with the following number. So after all is compensate for in A1, cell C1 should have 8410. Does this make any sense to anyone?

You did not answer one of my questions... what if the number in Column A has a 0 in it? For example, what value would you want in Column B if the number in Column A was 305 for example?

 

[Marcelo Branco]

You did not answer one of my questions... what if the number in Column A has a 0 in it? For example, what value would you want in Column B if the number in Column A was 305 for example?

Good question. I assumed it should be 5.

@ Mypaperdue
Could you, please, clarify?

M.

 

[Rick Rothstein]

Good question. I assumed it should be 5.

So 5 produces 10 (not 0) but 0 produces 10... seems strange to me. I would think 5 produces 0 and 0 produces 5 would make more sense given how the rest of the encoding is set up.

 

[Marcelo Branco]

So 5 produces 10 (not 0) but 0 produces 10... seems strange to me. I would think 5 produces 0 and 0 produces 5 would make more sense given how the rest of the encoding is set up.

Yes, i think so.
0 --> 5
5 --> 0

Then my formula for 1 or 2 or 3-digit numbers would be (returns text, not numbers)

	A​	B​	C​
1​	503​		058​
2​	50​		05​
3​	5​		0​
4​	467​		912​
5​	46​		91​
6​	4​		9​
7​	100​		655​
8​	10​		65​
9​	1​		6​

C1 copied down
=MOD(MID(A1,1,1)+5,10)&IFERROR(MOD(MID(A1,2,1)+5,10),"")&IFERROR(MOD(MID(A1,3,1)+5,10),"")

M.

 

[Mypaperdue]

The way I have it set it, 0 can never occur in column A. That's why i didn't put so much emphasis with 0.