Converting from base 10 to base x Reply

This is a formula-based solution which, given a positive number in base 10, converts that number into its equivalent form for another, given positive base.

Convert from base 10 to base x

So, given a base-10 value to be converted in A2, and the base to which we wish to convert this value in B2, the required output is generated in C2. Cell D2 contains a “back-check” which re-converts the value in C2 to base 10.

The formula in C2 is:

=SUMPRODUCT(MOD(FLOOR(A2/B2^(ROW(INDIRECT("1:20"))-1),1),B2)*10^(ROW(INDIRECT("1:20"))-1))

And the back-check formula in D2 is:

=SUMPRODUCT(B2^(ROW(INDIRECT("1:"&LEN(C2)))-1),0+MID(C2,1+LEN(C2)-ROW(INDIRECT("1:"&LEN(C2))),1))

How does it work?

Since a number in any base, x say, is by definition of the form:

a0x0+a1x1+a2x2+…+anxn

we simply need to determine what each of the constants a0, a1, a2,…,an are.

As an example, and taking base 10 since we are all familiar with it, let’s take the value 173. We’re all fully aware that this value represents (in base 10):

(1×100)+(7*10)+(3*1)

which of course can also be written:

(1×10^2)+(7*10^1)+(3*10^0)

(For those who don’t already know, any non-zero value to the power of zero is 1.)

We can make this breakdown easily enough. But how can we instruct Excel to do so? What instructions can we give it so that, given a value such as 173, we can generate a series of returns, i.e. 1, 7 and 3 corresponding respectively to the coefficients of 10^2, 10^1 and 10^0? All that we need to do is to somehow find a way to translate the value 173 as being made up of 1 “hundred”, 7 “tens” and 3 “units”.

The way (most) human minds would approach this would be to reason along the lines of:

There is 1 “hundred” in 173.
When we take off this 1 “hundred”, the remainder, 73, consists of 7 “tens”.
When we take off these 7 “tens”, the remainder, 3, consists of 3 “units”.

All very elementary stuff. And of course we could quite simply generate an equivalent algorithmic process just like the above within Excel. The only snag is that each of the lines in the above algorithm is dependent upon the previous. What I mean is that, if we were to set it up thus:

Picture2

which is a quite natural attempt to translate our reasoning above, then, were we to now attempt to condense this into a single formula, we would most likely struggle, the reason being that each of the formulas in column D contain references to values in the row immediately above.

As such, working down the chain, if we wished to obtain the “units” value of 3 from our initial value of 173 using a single formula, we would be looking at something like:

=INT(D2-(INT(D2/10^2)*10^2)-(INT((D2-(INT(D2/10^2)*10^2))/10^1)*10^1)/10^0)

which is obviously far from ideal, not to mention the fact that we are here only considering a case in which the largest required index for our base is 2: imagine if instead we were looking at such a formula for a 12-digit number.

Fortunately there is an alternative method for obtaining each of these values. What’s more, it is one which does not suffer the drawback that the calculation for each of these values is dependent upon the previous value.

Using our example of 173, this time let’s calculate the result obtained by dividing that value by each of 100, 10 and 1, taking the integer portion of those values, and then finally finding the remainder when each of these new values is divided by our base, 10, i.e.:

Picture3

And here we can see that each of the values in the Result column, unlike in the previous set-up, does not depend upon the values in previous rows. This time, the single formula for obtaining our “units” value of 3 would be simply:

=MOD(INT($A$2/10^0),10)

And so this should lead us to the solution, since all we now need to do is construct an array version of the above. The only thing we need to decide upon is over what size array we perform the calculation, i.e. what is the largest required index in our given base?

In the above example we clearly needed to go as far as the “hundreds”; hence an array consisting of three elements was required for our powers of 10: 0, 1 and 2. If we were instead considering a value such as 10,301,444 then clearly we would be required to go as far as 10^7, and so our array would consist of 8 elements (from 10^0 to 10^7).

In fact, I decided on a rather “lazy” approach here. Rather than determine this value dynamically, I simply used a reasonably large (in Excel terms at least) upper bound for the indices of 20. Readers may wish to investigate for themselves a more refined set-up which does involve a dynamic clause for determining the minimum required upper bound.

And of course this process is no different when we are using a base other than 10. To take the example given at the very beginning, i.e. convert 552 into base 4, this part of the formula:

B2^(ROW(INDIRECT("1:20"))-1)

would resolve to an array consisting of the 20 values obtained via raising 4 to the power of each of the integers from 0 to 19, i.e.

{1;4;16;64;256;1024;4096;16384;65536;262144;1048576;4194304;16777216;67108864;268435456;1073741824;4294967296;17179869184;68719476736;274877906944}

and so we divide 552 by each of these and take the integer portion, so that:

FLOOR(A2/{1;4;16;64;256;1024;4096;16384;65536;262144;1048576;4194304;16777216;67108864;268435456;1073741824;4294967296;17179869184;68719476736;274877906944},1)

becomes:

{552;138;34;8;2;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}

Finding the remainder when each of these is divided by 4 is achieved by MOD, so that:

MOD({552;138;34;8;2;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0},B2)

resolves to:

{0;2;2;0;2;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}

The second array in the product is straightforward enough, obtained by simply raising 10 to the power of our array of integers from 0 to 19, i.e.:

{1;10;100;1000;10000;100000;1000000;10000000;100000000;1000000000;10000000000;100000000000;1000000000000;10000000000000;100000000000000;1000000000000000;10000000000000000;100000000000000000;1000000000000000000;10000000000000000000}

amd so, finally:

=SUMPRODUCT({0;2;2;0;2;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}*{1;10;100;1000;10000;100000;1000000;10000000;100000000;1000000000;10000000000;100000000000;1000000000000;10000000000000;100000000000000;1000000000000000;10000000000000000;100000000000000000;1000000000000000000;10000000000000000000})

gives 20220, which, even without the back-check formula we can easily verify is correct, since this represents, in base 4:

(2*4^4)+(0*4^3)+(2*4^2)+(2*4^1)+(0*4^0)

which is:

(2*256)+(0*64)+(2*16)+(2*4)+(0*1)

i.e.:

512+0+32+8+0

which is 552.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s