Actually, stealing the comparison approach Bernard used (leave the negative
value negative and make the last two digits negative instead), we get these
even shorter formulas...

If there are no spaces....

=IF(-RIGHT(A1,2)=MOD(SUMPRODUCT(MID(A1,ROW(A1:A7),1)*(9-ROW(A1:A7))),97)-97,"Valid","Invalid")

If there could be spaces in the VAT number, and if they are in the positions
shown when present...

=IF(-RIGHT(A1,2)=MOD(SUMPRODUCT(MID(SUBSTITUTE(A1," ",""),
ROW(A1:A7),1)*(9-ROW(A1:A7))),97)-97,"Valid","Invalid")

--
Rick (MVP - Excel)



"Rick Rothstein" wrote in message
...
Okay, here is yet another approach for you to try.

If there are no spaces within the VAT number, then try this...

=IF(--RIGHT(A1,2)=ABS(MOD(SUMPRODUCT(MID(A1,ROW(A1:A7),1 )*(9-ROW(A1:A7))),97)-97),"Valid","Invalid")

If there could be spaces in the VAT number, and if they are in the
positions shown when present, then use this formula instead...

=IF(--RIGHT(A1,2)=ABS(MOD(SUMPRODUCT(MID(SUBSTITUTE(A1," ",""),
ROW(A1:A7),1)*(9-ROW(A1:A7))),97)-97),"Valid","Invalid")

--
Rick (MVP - Excel)



"Rob" wrote in message
...
Hi,

I want to check a number of VAT numbers using a formula, the first check
is to ensure the number is 9 digits (may need to remove spaces to ensure
clean data). The process is then to apply the below criteria which has
proved difficult for he to accomplish.

Any help will be most welcome. Thanks, Rob
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 339 0727 47

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.

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.