1 2 Previous Next 21 Replies Latest reply on Mar 26, 2012 9:10 AM by olgagutten

# Calculation to substitute the numbers

Dear All,

I need help to solve this calculation:

1. I have 8 digit unsorted number, I need to find least significant number from right to left and multiply by 2,

2. The next least significant number is multiplied by 3,

3. The next least significant number is multiplied by 4 and so on.

The results of these multiplications are added up into sum.

I have already tried to do the custom function: to sort the numbers in ascending order like (SortValues ( list ; sortAscending )); and then substitude function to substitude each subsequent number by that calculation, but it does not work. It will not even allow me to create the custom sort function.

Could anyone, please, help me ?

• ###### 1. Re: Calculation to substitute the numbers

Can you show us the calculation you tried?

• ###### 2. Re: Calculation to substitute the numbers

olgagutten wrote:

I have already tried to do the custom function: to sort the numbers in ascending order

I don't think you need to sort at all. Try =

2 * Mod ( number ; 10 ) +

3 * Mod ( Div ( number ; 10 ) ; 10 ) +

4 * Mod ( Div ( number ; 10^2 ) ; 10 ) +

5 * Mod ( Div ( number ; 10^3 ) ; 10 ) +

6 * Mod ( Div ( number ; 10^4 ) ; 10 ) +

7 * Mod ( Div ( number ; 10^5 ) ; 10 ) +

8 * Mod ( Div ( number ; 10^6 ) ; 10 ) +

9 * Mod ( Div ( number ; 10^7 ) ; 10 )

Or you could use a custom function to iterate over the digits.

• ###### 3. Re: Calculation to substitute the numbers

Instead of a custom function, you could:

1) create a repeating calculation field ( cRep ) with 8 repeatitions and with calc:

( Get ( CalculationRepetitionNumber ) + 1 ) * Mod ( Div ( Extend ( number ) ; 10 ^ ( Get ( CalculationRepetitionNumber )  - 1 ) ) ; 10 )

2) create a result ( total ) calculation field:

Sum ( cRep )

Note that:

the cRep calculation field do not need to stay on the layout

A 8 digit number will return a result with: 0 ≤ result ≤ 396

• ###### 4. Re: Calculation to substitute the numbers

Hello Guys,

Thank you for your posts. None of these formulas are working properly.

For Example I have this number:  11119876, Using this formula:

2 * Mod ( number ; 10 ) +

3 * Mod ( Div ( number ; 10 ) ; 10 ) +

4 * Mod ( Div ( number ; 10^2 ) ; 10 ) +

5 * Mod ( Div ( number ; 10^3 ) ; 10 ) +

6 * Mod ( Div ( number ; 10^4 ) ; 10 ) +

7 * Mod ( Div ( number ; 10^5 ) ; 10 ) +

8 * Mod ( Div ( number ; 10^6 ) ; 10 ) +

9 * Mod ( Div ( number ; 10^7 ) ; 10 )

I am getting result : 140 which is wrong,  where the number must be: 543272644936-this is if you read from right to left, the first min number would be 1, it needs to be multiplied by 2, so result is 2;

the next it starts reading from right to left again omitting that first result, the next min number is 1 again, multiplied by 3, so result is 3;

the next it starts reading from right to left again, omitting the second result which is now 3, the next min number is then 1 again, multiplied by 4 now, so result is 4; and so on.

In the second formula: the result is : 12, it means it takes the first number from left to right and mulitplies it by 2 and does not go any further. . .

Instead of a custom function, you could:

1) create a repeating calculation field ( cRep ) with 8 repeatitions and with calc:

( Get ( CalculationRepetitionNumber ) + 1 ) * Mod ( Div ( Extend ( number ) ; 10 ^ ( Get ( CalculationRepetitionNumber )  - 1 ) ) ; 10 )

2) create a result ( total ) calculation field:

Sum ( cRep )

Note that:

the cRep calculation field do not need to stay on the layout

A 8 digit number will return a result with: 0 ≤ result ≤ 396

• ###### 5. Re: Calculation to substitute the numbers

olgagutten wrote:

For Example I have this number:  11119876,

if you read from right to left, the first min number would be 1, it needs to be multiplied by 2,

I am afraid you may be confusing between "least significant" and "smallest". In your example, the least significant digit is 6, because it represents the value of 9 at most. The digit 1 you are referring to (the one immediately to the left of 9) stands for a value of 10,000 - and could go as high as 90,000.

Perhaps you could point us to the source of your algorithm. To me, it seems like you are trying to calculate a checksum?

• ###### 6. Re: Calculation to substitute the numbers

I agree, Michael, that it looks similar to the check-digit algorithm used in the old ISBN or a vehicle VIN.  The source of the algorithm would be helpful.

• ###### 7. Re: Calculation to substitute the numbers

Yes, Michael. It is a check digit formula, I have got it wrong myself initially. Do you have any proper examples, I need to figure out modulus 19 on check digit. It is like and ISBN.

• ###### 8. Re: Calculation to substitute the numbers

This is working great now. You are genious Michael. Thank you very very much!!!!

• ###### 9. Re: Calculation to substitute the numbers

Is it the new ISBN (13 digits) or the old ISBN (10 digits)?  The old ISBN was MOD 11 and the new ISBN is MOD 10.  You need to give us the algorithm.

• ###### 10. Re: Calculation to substitute the numbers

Dear All,

I have solved the algorithm in number of steps. I will describe it here, but I am looking for more elegant and short way using for example Let function of Recursive function. The purpose is to input into one calculation instead of 5, or at least into 2 calculations.

I have alpha-numeric number FDXC5647K

1. Each alpha character is converted into a number according to the position in alphabet, for example:

Substitute(Middle ( number; 1 ; 3);

["A";101];

["B";102];

["C";103];

["D";104];

["E";105];

["F";106];

["G";107];

["H";108];

["I";109];

["J";110];

["K";111];

["L";112];

["M";113];

["N";114];

["O";115];

["P";116];

["Q";117];

["R";118];

["S";119];

["T";120];

["U";121];

["V";122];

["W";123];

["X";124];

["Y";125];

["Z";126]) - This is currently done 4 times to get separate results of each conversion of FDXC5647K

2. The result of each convertion is then miltiplied by 9, next result is by 8 etc. untill and the rest 4 digits of the initial number is multiplied by: FDXC5647K - 5 by 5, 6 by 4, 4 by 3 and 7 by 2.

3. All of this are then totaled and modulus of 13 is found.

I had to create 4 separate calculation fields to convert those 4 initial characters.

4. To get total result I had to do the following:

Middle (number;10; 1) * 2+

Middle(number;9; 1)  * 3+

Middle(number;8; 1) * 4 +

Middle(number;7; 1 ) * 5+

number_one * 6+

number_two 3 * 7+

number_three  * 8+

number_four * 9)

where number_one, number_two, number_three and number_four are those separate calculation results with first conversions.

number-is an input field for  FDXC5647K

The modulus is then found from the total which I have no problem with.

• ###### 11. Re: Calculation to substitute the numbers

I have lost you at step 2. Please provide a worked-out example. Note that "first", "second", etc. depend on the direction you have chosen.

• ###### 12. Re: Calculation to substitute the numbers
F1069954
D1048832
X1247868
C1036618
55525
66424
44312
77214
K1111111

Sum ( ) = 3458 ;

Mod ( 3458 ; 19 ) = 0

Is this the correct method?

• ###### 13. Re: Calculation to substitute the numbers

This is right, exept K is a check character.

• ###### 14. Re: Calculation to substitute the numbers

olgagutten wrote:

exept K is a check character.

Not sure what this means in terms of the required process.

1 2 Previous Next