Formula to convert 4 digits number to digits number !!

[serge]

Hi everyone,
I'm pretty sure it's impossible but I'm gone ask anyway.
I wanted to now if some how a 4 digits number ( between 1000:7561 ) could be converted to a 3 digits number ( Between 100:999), and that I can with that conversion formula I could return to the original 4 digits number ?
Than you.

 

[awoohaw]

I am pretty sure if you remove significant digits from a number you can not regain the precision of the original number.
Do you have an algorithm that can be used in a formula?

 

[serge]

No.
What it is is that I created 2 excel system than one use 4 digits and the other 3 digits, the one that use 3 digits give me less selections to select from witch reduce enormously the error factor than the one with 4, so that's why I'm try to bring it down to 3 digits.

 

[awoohaw]

then you'll need to show what you're doing. Please provide some examples, and explain how it is done from one system to the other.

 

[serge]

Than you awoohaw,
Example of my system will not help for what I'm asking here, I'm just trying to downsize 4 digits to 3, I need to find a solution, I don't know how but I will by dividing, misusing or else or using square root, digital root or using reference tables, etc...

 

[Phuoc]

You can use: BASE(Number, Radix, [Min_length])

Radix between 22 to 31.

 

[KRice]

As @awoohaw has indicated, if you are limited to using base 10 (numerals 0..9 as the digits), then you'll run into difficulties trying to do this. However, as @Phuoc has suggested, if you are willing to accept that a "digit" can be something other than 0..9, then you can use the built-in BASE and DECIMAL functions to convert back and forth. Here is an example with Radix=22 showing 4-digits in one system (base 10) converted into 3-digits in another system (base 22), and then converted back into base 10 to recover the initial number.

I favor using a Radix=20 for this application.

Book2
	A	B	C	D	E	F	G
1	4-digit	3-digit (base 22)	convert back
2	1000	21A	1000		1000	21A	1000
3	1100	260	1100		1001	21B	1001
4	1200	2AC	1200		1002	21C	1002
5	1300	2F2	1300		1003	21D	1003
6	1400	2JE	1400		1004	21E	1004
7	1500	324	1500		1005	21F	1005
8	1600	36G	1600		1006	21G	1006
9	1700	3B6	1700		1007	21H	1007
10	1800	3FI	1800		1008	21I	1008
11	1900	3K8	1900		1009	21J	1009
12	2000	42K	2000		1010	21K	1010
13	2100	47A	2100		1011	21L	1011
14	2200	4C0	2200		1012	220	1012
Sheet1

Cell Formulas
Range	Formula
A2:A68	A2	=SEQUENCE(67,,1000,100)
B2:B68,F2:F16	B2	=BASE(A2#,22)
C2:C68,G2:G16	C2	=DECIMAL(B2#,22)
E2:E16	E2	=SEQUENCE(15,,1000,1)
Dynamic array formulas.

 

[Peter_SSs]

@Phuoc @KRice
If the OP's profile is correct then I don't think they would not be able to use BASE (or DECIMAL) as my understanding is those function were only introduced in Excel 2013.

 

[KRice]

Totally missed that, Peter!...right you are. Well, if working in a different base works for the OP, then I suppose a UDF/VBA solution is an option, or a worksheet could be set up to perform the conversions:

MrExcel_20240217.xlsx
	A	B										L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
1	4-digit	3-digit (base 20)											400	20	1				Base20					Base10		Lookups	all text
2	1000	2A0											2	10	0	2	A	0	2A0		2	A	0	1000		0	0	0
3	1001	2A1											2	10	1	2	A	1	2A1		2	A	1	1001		1	1	1
4	1002	2A2											2	10	2	2	A	2	2A2		2	A	2	1002		2	2	2
5	1003	2A3											2	10	3	2	A	3	2A3		2	A	3	1003		3	3	3
6	1004	2A4											2	10	4	2	A	4	2A4		2	A	4	1004		4	4	4
7	1005	2A5											2	10	5	2	A	5	2A5		2	A	5	1005		5	5	5
8	1006	2A6											2	10	6	2	A	6	2A6		2	A	6	1006		6	6	6
9	1007	2A7											2	10	7	2	A	7	2A7		2	A	7	1007		7	7	7
10	1008	2A8											2	10	8	2	A	8	2A8		2	A	8	1008		8	8	8
11	1009	2A9											2	10	9	2	A	9	2A9		2	A	9	1009		9	9	9
12	1010	2AA											2	10	10	2	A	A	2AA		2	A	A	1010		10	A	10
13	1011	2AB											2	10	11	2	A	B	2AB		2	A	B	1011		11	B	11
14	1012	2AC											2	10	12	2	A	D	2AD		2	A	D	1012		12	C	12
15	1013	2AD											2	10	13	2	A	D	2AD		2	A	D	1012		12	D	12
16	1014	2AE											2	10	14	2	A	E	2AE		2	A	E	1014		14	E	14
17	1015	2AF											2	10	15	2	A	F	2AF		2	A	F	1015		15	F	15
18	1016	2AG											2	10	16	2	A	G	2AG		2	A	G	1016		16	G	16
19	1017	2AH											2	10	17	2	A	H	2AH		2	A	H	1017		17	H	17
20	1018	2AI											2	10	18	2	A	I	2AI		2	A	I	1018		18	I	18
21	1019	2AJ											2	10	19	2	A	J	2AJ		2	A	J	1019		19	J	19
22	1020	2B0											2	11	0	2	B	0	2B0		2	B	0	1020
Sheet1

Cell Formulas
Range	Formula
M1:O1	M1	=(20^{2,1,0})
A2:A6571	A2	=SEQUENCE(6570,,1000,1)
B2:B6571	B2	=BASE(A2#,20)
M2:M22	M2	=INT(A2/400)
N2:N22	N2	=INT((A2-M2*400)/20)
O2:O22	O2	=A2-M2*400-N2*20
P2:R22	P2	=VLOOKUP(M2,$Z$2:$AA$21,2)
S2:S22	S2	=P2&Q2&R2
U2:U22	U2	=LEFT(S2,1)
V2:V22	V2	=MID(S2,2,1)
W2:W22	W2	=RIGHT(S2,1)
X2:X22	X2	=SUMPRODUCT(VLOOKUP(U2:W2,$AA$2:$AB$21,2),{400,20,1})
Dynamic array formulas.

Clunky, but it works...although I would recommend a VBA solution. I see that @Rick Rothstein has already tackled that here:

Convert a Number in Any Base (Up To 36) to a (Very Large) Positive Decimal Number

See this article for the complimentary function Dec2Base... Convert a (Possibly) Very Large Decimal Number to Any Base (Up To 36) This is probably more of a novelty function than anything else, but I figured I would share it with the readers of this forum anyway. The function will take any...

www.excelfox.com

Function Base2Dec(BaseDigits As String, BaseNumber As Long) As Variant
    Dim X As Long, Z As Long, DigitVal As Variant, Power As Variant
    Const PossibleDigits = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"
    If Not UCase(BaseDigits) Like "*[!" & Left(PossibleDigits, BaseNumber) & "]*" Then
        For X = 0 To Len(BaseDigits) - 1
            DigitVal = UCase$(Mid(BaseDigits, Len(BaseDigits) - X, 1))
            DigitVal = InStr(PossibleDigits, DigitVal) - 1
            Power = 1
            For Z = 1 To X
              Power = CDec(Power) * BaseNumber
            Next
            Base2Dec = Base2Dec + DigitVal * Power
        Next
    Else
        Err.Raise 9999, , "Bad base digit specified"
    End If
End Function

...and here:

Convert a (Possibly) Very Large Positive Decimal Number to Any Base (Up To 36)

Back in my "Convert a Number in Any Base (Up To 36) to a (Very Large) Decimal Number" article, I posted a function that would convert a number in any base to a decimal number where that number could contain up to 29 decimal digits... the following function is the compliment to it... with it, you...

www.excelfox.com

Function Dec2Base(DecimalValue As Variant, Base As Long) As String
  Const PossibleDigits = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"
  DecimalValue = CDec(DecimalValue)
  Do Until DecimalValue = 0
    Dec2Base = Mid(PossibleDigits, CDec(DecimalValue) - Base * _
               Int(DecimalValue / Base) + 1, 1) & Dec2Base
    DecimalValue = Int(CDec(DecimalValue) / Base)
  Loop
End Function

 

[serge]

Wowww you guys are something else, I can't believe how far you go to help me and I really really appreciate it, but after seeing all your dedicated work I guess it will not be possible.
in column B I would need to have a base 10, 3 digits number in return.
Again thank you all very much for having taking the time to help me.