Hi All, I need some help on creating a formula.
I am currently checking the VAT num of our customer and would like to have a formula which check the Validity and return a VALID or NOT VALID in a cell.( see excel file ). VAT registration numbers should be tested as follows: 1. The *first seven digits* of the VAT registration number are *listed vertically* 2. Each digit is multiplied by a number, starting with 8 and decreasing to 2. 3. The sum of the multiplications is calculated. 4. *97* is subtracted from the sum as many times as is necessary to arrive at a negative number. 5. The negative number should be the same as the last 2 digits of the VAT registration number if it is valid. *Example* VAT registration number *GB339072747* 3 *8 = 24 3 *7 = 21 9 *6 = 54 0 *5 = 0 7 *4 = 28 2 *3 = 6 7 *2 = 14 Total = 147 147 - 97 = 50 - 97 = - 47 As the negative number(- 47) is the same as the last two digits of the VAT number, the number is valid. I have attached an xls file where u can add the formula. Thanks so much for your help Regards Nourredine BELHAJ +41783081988 +33665248617 -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
VAT CHECK.xls
Description: MS-Excel spreadsheet
