Reverse formula.

[serge]

Hello,

I'm lookking for a formula that will give me the result in reverse, like if I type 1203 in A3 it will return 99 in H3. Here the formulas that transfer the numbers. C3 IF($B3="","",INT(LEFT(MOD(SUM($A3/64),4)))) D3 IF($B3="","",INT(LEFT(MOD(SUM($A3/16),4)))) E3 IF($B3="","",INT(LEFT(MOD(SUM($A3/4),4)))) F3 IF($B3="","",INT(LEFT(MOD(SUM($A3/1),4)))) Thank you.

 

[StephenCrump]

	A	B
1	1203	99
2	1023	75
3	121	25
4	0	0
Sheet1

Cell Formulas
Range	Formula
B1:B4	B1	=DECIMAL(A1,4)

 

[serge]

Hi stephenCrump,

I believe that excel 2007 doesn't support "DECIMAL" does it ?
Would another formula do the trick ?
I appreciate your help.
Thank you.

 

[Peter_SSs]

For numbers up to 6 digits, try ..

23 10 10.xlsm
	A	B
1	1203	99
2	1023	75
3	121	25
4	0	0
5	333333	4095
Convert

Cell Formulas
Range	Formula
B1:B5	B1	=SUMPRODUCT(MID(TEXT(A1,REPT(0,6)),{1,2,3,4,5,6},1)*(4^{5,4,3,2,1,0}))

 

[bebo021999]

Or, use this:

=SUMPRODUCT(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*4^(LEN(A1)-ROW(INDIRECT("1:"&LEN(A1)))))

 

[StephenCrump]

I believe that excel 2007 doesn't support "DECIMAL" does it ?

Sorry, I thought DECIMAL (and BASE) had been around forever. Only 10 years!

 

[serge]

Thank you to all of you, your formulas work great, I really appreciate the help.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.