Trying to find a simple Math operation for this transformation.

[albert_e]

I am using an excel sheet to generate some URLs that need a input number encoded.

I figured out the pattern -- it is a simple digit manipulation

Input --> Encoded Output
-------- ----------------------
271678 --> 01303032373136373875
261268 --> 01303032363132363875
220091 --> 01303032323030393175
202995 --> 01303032303239393575
15020802 --> 01313530323038303275
269154 --> 01303032363931353475


Output = 013 + D1 + 3 + D2 + 3 + D3 + 3 + D4 + 3 + D5 + 3 + D6 + 3 + D7 + 3 + D8 + 75

(the above is "string" append operation ... not arithmetic addition)

where D1 through D8 are digits in the input number (padded with leading zeroes to make it 8 digits)



Can you help me find a simple math operation that will do this input to output transformation?


I can do it using Remainder/Modulo operations but it will be very tedious.


I can see the problem as this:


Output, y = 01303030303030303075 + f(x)*100

(arithmetic addition here)


Where the function f(x) "explodes" the input number x as in the example below ...


f(00123456) = 000010203040506



Do we have an elegant definition for f(x)?



Thanks in advance!

 

[lluke9]

"Glossary"... kind of important
--------------------------
x ⇔ the 8-digit number.
Dn ⇔ Digit.
z ⇔ place-value exponent of a given Dn. (Dn x 10^z)
n ⇔ number label of any digit Dn.
m ⇔ 9 - n. Specifies to what multiple x should be rounded down to given n.
R(x) or any variations thereof ⇔ Round down function. Can be given in terms of the truncation function, T(x). SEE A.
T(x) or any variations thereof ⇔ Truncation function. Can be given in terms of the floor function.
--------------------------
NOTE: I use the "round down" function here, which may or may not be the same thing as ROUNDDOWN in Excel. Probably not, I don't know the slightest thing about Excel.
--------------------------
This is how Wikipedia defined round_m(x), where round(x) is defined as "rounding x to the nearest multiple of m" or "round x to the nearest mth":
"Divide x by m, let the result be y;
Round y to an integer value, call it q;
Multiply q by m to obtain the rounded value."

So rounding 15 with m = 10, or rounding 15 to the 10ths place:
Divide x = 15 by m = 10... y = 1.5.
Round y = 1.5 to the nearest integer... q = 2.
Multiply q = 2 by m = 10... 20.

And...

A
This is how I'll define my round down function R_m(x), which means "round down x to the nearest mth's":
Divide x by m, let the result be y;
Truncate y to 0 decimal places, call it q;
Multiply q by m to obtain the rounded value.

So if you want to do this for 00123456 for m = 10⁴, you would divide first:
00123456/(10⁴) = 0012.3456

then truncate:
T(0012.3456) = 0012

then multiply by m = 10^4
0012 * 10^4 = 00120000.

So R_10⁴(00123456) = 00120000.

You'll see where I'm going with this.

But basically: $R_{10^m}(x) = m*T(\frac{x}{m})$
I believe in Excel-language that would be m*TRUNC(whatevercellhas[x/m], 0)
--------------------------

NOW:

Most importantly, we know that x has 8 digits:

{D1|D2|D3|D4|D5|D6|D7|D8} ... which I will denote like so for the sake of organization.

Expanded, it looks like this:
D1*10⁷ + D2*10⁶ + D3*10⁵ + ... + D8*10⁰

First off, D8 can be extracted as a single digit by taking x, and rounding down x to the place value to the left of D8 -- otherwise stated as rounding x down to the nearest multiple of 10¹ and written as
R_10¹(x)).

The operation returns this number:
{D1|D2|D3|D4|D5|D6|D7|0|}

Which when subtracted from x, or {D1|D2|D3|D4|D5|D6|D7|D8|}, returns {D8}.

So written concisely,
D8 = x - R_10¹(x).

Obviously this only extracts a single-digit number in the sole case of D8, (ex: 12345678 - 12345000 = 678 doesn't work), and for everything else it extracts every digit to the right of that one.
So we need to cut off some numbers. For m = 1, it returns one digit, and for m = 2 it returns two. We only want the left-most digit, so we can cut it off by dividing by 10^m-1 (note that after taking the difference of x and rounded x, there remains only m digits, so removing m-1 of them is appropriate) and truncating the decimals:

$T(\frac{x-R_{10^m}(x)}{10^{m-1}}) = Dn$

Now, note that:
D8 -> uses m = 1 for extraction of digit
D7 -> uses m = 2 for extraction of digit
.
.
.

D1 -> uses m = 8 for extraction of digit

So for any digit Dn, m + n = 9.

Also note that:
D8 is raised to the 10⁰ in x.
D7 is raised to the 10¹ in x.
.
.
.
D1 is raised to the 10⁷ in x.

So for any digit Dn, n + z = 8, where z is the original power it was raised to.

n = 9 - m
n = 8 -z
z = m - 1.THIS IS SUPER IMPORTANT THIS IS SUPER IMPORTANT THIS IS SUPER IMPORTANT
f(x) is the sum of all the digits times 10 raised to the original power z x 2...
In other words:
$f(x) = \sum_{n=1}^{8} Dn^{2z}$
Don't believe me? Try it out yourself. Every digit, in essence, is shifted up twice its original place (where ones-place = 0, tens-place = 1, hundreds-place = 2, etc.)
It's great that it worked out so easily, or else our f(x) would be terribly ugly.
THIS IS SUPER IMPORTANT THIS IS SUPER IMPORTANT THIS IS SUPER IMPORTANT

Now we simply compile everything we know:

As I already showed, $T(\frac{x-R_{10^m}(x)}{10^{m-1}}) = Dn$.
We can substitute that in, and write it all in terms of m using the the relation:
z = m - 1.

This is it:

$f(x) = \sum_{m=1}^{8} T\left (\frac{x-R^-_{10^m}(x)}{10^{m-1}} \right )10^{2(m-1)}$

"Glossary"... added here again for convenience
--------------------------
x ⇔ the 8-digit number.
Dn ⇔ Digit.
z ⇔ place-value exponent of a given Dn. (Dn x 10^z)
n ⇔ number label of any digit Dn.
m ⇔ 9 - n. Specifies to what multiple x should be rounded down to given n.
R(x) or any variations thereof ⇔ Round down function. Can be given in terms of the truncation function, T(x).
T(x) or any variations thereof ⇔ Truncation function. Can be given in terms of the floor function.
--------------------------

I'm pretty sure I've made some dumb mistake somewhere, but you get the general idea. Round, subtract, truncate, multiply it by double the magnitude of the original, rinse and repeat 7 more times, add 'em all up from D1 to D8. Also, I'm not sure what kind of math this is categorized under, but it sure was fun working it out.

 

[Mark44]

Input --> Encoded Output
-------- ----------------------
271678 --> 01303032373136373875
261268 --> 01303032363132363875
220091 --> 01303032323030393175
202995 --> 01303032303239393575
15020802 --> 01313530323038303275
269154 --> 01303032363931353475 Output = 013 + D1 + 3 + D2 + 3 + D3 + 3 + D4 + 3 + D5 + 3 + D6 + 3 + D7 + 3 + D8 + 75

I'm going to assume that your input is a string of eight digit characters (as opposed to an eight-digit number), and that the output is a 20-character string.

To do this interleaving, it's helpful to use two arrays and a loop that pulls characters from one array and stuffs them in the right place in the other array.

Here's some C code that should work. Most programming languages would work, and you could probably do this in a VBA macro in Excel.

I've made my arrays one character longer than needed so as to be able to store a null character (ASCII 0) at the end, to mark the end of a string. The C standard library functions rely on the presence of a null character at the end

char input[9] = "00271678";
char output[21];

output[0] = '0';
output[1] = '1';
output[2] = '3'

for (int j = 0; j < 6; j++)
{
   output[2 * j + 3] = input[j];
   output[2 * j + 4] = '3';
}
output[15] = input[6];
output[16] = '3'; 
output[17] = input[7];
output[18] = '7';
output[19] = '5';
output[20] = '\0';  // null character at end of string

Edit: I think I'm off by one character in my code. I don't have time to look at it right now, but will take another look in a couple of hours.

Edit2: I think this works now. The code before the loop and after have to take into account the initial "013" substring and the "75" substring and the null character at the end.

 

[albert_e]

Thanks friends,

lluke - thanks for working it out. I think I get the essence of your solution: extract the digit from 10^n position, and multiply it with 10^2n -- and iterate & accumulate the sum.

Mark44 - thanks to you too.

I believe the current excel sheet I have does the same thing in essence, in multiple intermediate steps:
1. extract each digit D1 through D8 into individual cells and 2. use a string append formula to interleave '3' and the separate digits.

What I was hoping to find was a more elegant "single step" mathematical operation.

Lets say Cell A1 in Excel has the value '123456'
Can I put any elegant formula in Cell A2 that gives me the value '10203040506'

is there any mathematical trick that gives me
f(12) = 102
f(123) = 10203
f(1234) = 1020304

------------------

Thinking in slightly different terms:

Let me imagine a function f(x,n) that inserts an extra zero at position 10^n in the input number x.

f(1234,0) = 12340
f(1234,1) = 12304
f(1234,2) = 12034
f(1234,3) = 10234

I tried to explore using some thoughts like this ...

f(1234,1)
= 12304
= 12340 - 36
= 12340 - 4*9
= 1234*10 - 4*9

f(1234,2)
= 12034
= 12340 - 306
= 12340 - 34*9
= 1234*10 - (3*90+4*9)

f(1234,3)
= 10234
= 12340 - 234*9
= 1234*10 - (2*900 + 3*90 + 4*9)

... but didn't make much progress beyond this point.

I was hoping this pattern will fall into some beautiful simple math operation like multiply by 10 and subtract 9 something like that. Does this lead you anywhere?

 

[Mark44]

Thanks friends,

lluke - thanks for working it out. I think I get the essence of your solution: extract the digit from 10^n position, and multiply it with 10^2n -- and iterate & accumulate the sum.

Mark44 - thanks to you too.

I believe the current excel sheet I have does the same thing in essence, in multiple intermediate steps:
1. extract each digit D1 through D8 into individual cells and 2. use a string append formula to interleave '3' and the separate digits.

What I was hoping to find was a more elegant "single step" mathematical operation.

I doubt very much that such an operation exists.

Lets say Cell A1 in Excel has the value '123456'
Can I put any elegant formula in Cell A2 that gives me the value '10203040506'

is there any mathematical trick that gives me
f(12) = 102
f(123) = 10203
f(1234) = 1020304

As far as I know, no.

------------------

Thinking in slightly different terms:

Let me imagine a function f(x,n) that inserts an extra zero at position 10^n in the input number x.

f(1234,0) = 12340
f(1234,1) = 12304
f(1234,2) = 12034
f(1234,3) = 10234

I tried to explore using some thoughts like this ...

f(1234,1)
= 12304
= 12340 - 36
= 12340 - 4*9
= 1234*10 - 4*9

f(1234,2)
= 12034
= 12340 - 306
= 12340 - 34*9
= 1234*10 - (3*90+4*9)

f(1234,3)
= 10234
= 12340 - 234*9
= 1234*10 - (2*900 + 3*90 + 4*9)

... but didn't make much progress beyond this point.

I was hoping this pattern will fall into some beautiful simple math operation like multiply by 10 and subtract 9 something like that. Does this lead you anywhere?

Again, I don't think so. What you're trying to do is too "one-off" for a magical formula to exist. Formulas exist because there's a need for them. What you're trying to do can be done very easily with code using a loop.

 

[albert_e]

Thanks Mark44 for the response!

Appreciate the time.

Since I am dealing with a fixed length string, I will try and put it into a single line of javascript code. Dont think a loop is necessary,

Something like ..

Y = '013' + x[0] + '3' + x[1] + '3' + x[2] + '3' + x[3] + '3' + x[4] + '3' + x[5] + '3' + x[6] + '3' + x[7] + '75';

Do you think that would work?

I could then make a bookmarklet out of it I guess. I can right-click the input number on an existing page (which I am only a end-user of) and go to the target page in an instant.

I will have to initialize the string array x with a padded 8-digit version of the input number. will search around for how it can be done. Please let me know if you have some tips here. Thanks!

 

[jbriggs444]

Have you considered the obvious? If cell A34 contains the numeric that you want converted then...

=CONCATENATE("013",MID(TEXT(A34,"00000000"),1,1),"3",MID(TEXT(A34,"00000000"),2,1),"3",MID(TEXT(A34,"00000000"),3,1),"3",MID(TEXT(A34,"00000000"),4,1),"3",MID(TEXT(A34,"00000000"),5,1),"3",MID(TEXT(A34,"00000000"),6,1),"3",MID(TEXT(A34,"00000000"),7,1),"3",MID(TEXT(A34,"00000000"),8,1))

As displayed in the forums there may be spurious blanks in that formula. There are none in the original.

 

[Mark44]

Thanks Mark44 for the response!

Appreciate the time.

Since I am dealing with a fixed length string, I will try and put it into a single line of javascript code. Dont think a loop is necessary,

Something like ..

Y = '013' + x[0] + '3' + x[1] + '3' + x[2] + '3' + x[3] + '3' + x[4] + '3' + x[5] + '3' + x[6] + '3' + x[7] + '75';

Do you think that would work?

Sure. All you have done here is "unrolled" the loop that I showed in my code.

I could then make a bookmarklet out of it I guess. I can right-click the input number on an existing page (which I am only a end-user of) and go to the target page in an instant.

I will have to initialize the string array x with a padded 8-digit version of the input number. will search around for how it can be done. Please let me know if you have some tips here. Thanks!

You could calculate the length of the string that holds the input number. If the length is less than 8, prepend as many '0' characters as necessary to make a string with 8 digits.