Thanks, Harold I found a hint on http://microcoder.livejournal.com/17175.html that allows for a relatively simple formula in Calc if the invoice number has a fixed length. First make a table in a named space containing digits 0 1 2 3 4 5 6 7 8 9 in the first column and digits 0 2 4 6 8 1 3 5 7 9 in the second column. Then you can use that named space to lookup the reduced doubled values of uneven digits (even if the control digit is included). Starting from the rightmost digit, you alternately add up lookup value and the digits themselves. You need to subtract the mod(x;10) value from 10 to come up with the desired control digit. However, if the value is 0, the control digit is 0. Asuming the lookup table is named "doublev", and the invoice number is 14 digits long and placed in A1, you can calculate the control digit using this formula: =right(10-mod( vlookup(mid(A1;len(A1);1);doublev;2;0)+mid(A1;len(A1)-1;1)+ vlookup(mid(A1;len(A1)-2;1);doublev;2;0)+mid(A1;len(A1)-3;1)+ vlookup(mid(A1;len(A1)-4;1);doublev;2;0)+mid(A1;len(A1)-5;1)+ vlookup(mid(A1;len(A1)-6;1);doublev;2;0)+mid(A1;len(A1)-7;1)+ vlookup(mid(A1;len(A1)-8;1);doublev;2;0)+mid(A1;len(A1)-9;1)+ vlookup(mid(A1;len(A1)-10;1);doublev;2;0)+mid(A1;len(A1)-11;1)+ vlookup(mid(A1;len(A1)-12;1);doublev;2;0)+mid(A1;len(A1)-13;1); 10);1) add or remove elements to/from the bottom as required depending on length of invoice number. Use leading zeroes in invoice number to obtain a fixed length.
Karl Lausten "Harold Fuchs" <[email protected]> skrev i en meddelelse news:<[email protected]>... > 2009/3/9 Karl Lausten <[email protected]> > > > I need to calculate the check digit using the Luhn Algorithm (AKA Luhn 10 > > or > > Modulus 10) for a given number to oblige to format of the OCR line on the > > payment slip of Danish "FI-kort". This would allow creation of invoices > > with > > a valid payment slip identifying the debtor and payment purpose via a > > number > > automatically inserted on the bank account statement. > > I plan to use a spreadsheet with the required fields to create invoices > > using a mail-merge procedure. > > The calculation is possible using a rather complex formula. As the Luhn > > Algorithm also is used to validate Credit Card numbers and a number of > > other > > purposes I had hoped to find a function, that would easily calculate the > > required digit and perhaps a related function that would determine whether > > a > > given number is "valid" (ie check that the number passes the Luhn Algorithm > > test). > > Any suggestions on how to handle this challenge? Is there an AddOn > > somewhere, that would do this for me? (Have searched but not found) > > > > Thank you, > > Karl Lausten > > > > Googling Luhn gives any number of free downloads for software that will do > what you want but, as far as I can see, nothing for OOo. If you can get hold > of the actual formula then I'm sure it would not be hard to implement it as > a macro for Calc. For example I found > http://www.tech-faq.com/luhn-check-data-algorithm-in-java.shtml which is the > Java source code for a Luhn calculator. I don't have the capabilities but > perhaps you or someone on this list could take that code and incorporate it > into a Calc macro. The hard part, for me, is the interface between a Calc > cell and the Java routine. But that's not hard for some people :-) If you > are a programmer, > http://wiki.services.openoffice.org/wiki/API/Samples/Java/CalcAddin or, more > generally, http://api.openoffice.org/ might help you. > > -- > Harold Fuchs > London, England > Please reply *only* to [email protected] > --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
