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

    Calculation to substitute the numbers

    olgagutten

      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
          jbante

          Can you show us the calculation you tried?

          • 2. Re: Calculation to substitute the numbers
            comment

            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
              raybaudi

              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
                olgagutten

                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
                  comment

                  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
                    jason.delooze

                    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
                      olgagutten

                      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
                        olgagutten

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

                        • 9. Re: Calculation to substitute the numbers
                          jason.delooze

                          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
                            olgagutten

                            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
                              comment

                              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
                                comment
                                Header 1Header 2Header 3Header 4
                                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
                                  olgagutten

                                  This is right, exept K is a check character.

                                  • 14. Re: Calculation to substitute the numbers
                                    comment

                                    olgagutten wrote:


                                    exept K is a check character.

                                     

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

                                    1 2 Previous Next