See the attached file... I´ve user an array formula to solve... if you have any question fell free to ask.
2010/6/19 NOURREDINE BELHAJ <nourredine.bel...@gmail.com> > 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 > -- Fabio Lemos e-mail: flnle...@gmail.com -- ---------------------------------------------------------------------------------- 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
