Dear Frnd, may be this formula helping u....................
*=IF(ABS(((MID(A5,3,1)*8+MID(A5,4,1)*7+MID(A5,5,1)*6+MID(A5,6,1)*5+MID(A5,7,1)*4+MID(A5,8,1)*3+MID(A5,9,1)*2)-97*2))=ABS((MID(A5,10,2))),"Valid","Not Valid")* On Sat, Jun 19, 2010 at 4:14 PM, NOURREDINE BELHAJ < nourredine.bel...@gmail.com> wrote: > 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 > -- One Team One Dream One Goal Warm Regards, Vikas Chauhan E-Mail :- vikask...@gmail.com,vikask...@rediffmail.com, 9911868518, "We can't Spell S_ccess without U" Life is Very beautiful !!! ¨`•.•´¨) Always `•.¸(¨`•.•´¨) Keep (¨`•.•´¨)¸.•´ Smiling! `•.¸.•´. -- ---------------------------------------------------------------------------------- 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
Copy of VAT CHECK.xls
Description: MS-Excel spreadsheet
