validate data using formulae
 

i am making an employee form, and one cell must validate some information.
it can be manually, but if i can get excel to do it, it would be better. i'm
using office 07.

Cell E15 will have a 9 digit number (ex. 044-096-857)
the formula would validate this number and accept it or reject it (in our
case, this would be a valid number)
The formula would rewrite this number but needs to double every second
number. The rewritten number would be 08409128107. if you add up all of
these individual numbers, you get 40 - a multiple of 10, so the number is
valid and 044-096-857 would be validated and accepted in cell E15.

if E15 had 856-247-963, the formula would rewrite that to 810644149123.
this rewritten number adds up to 43 (not a multiple of 10, so an error would
be returned indicating invalid data.

any suggestions.

jat

validate data using formulae (Luhn algorithm)
 

the formulae is the Luhn Algorithm, and has many different variations. the
one thing that i know for sure is that i will have the number to validate in
E15. on wikipedia's site, i can download an excel format, but it shows a
table formula, something that i do not want. it also shows a vb code, but i
am not sure how i would integrate it into excel.

jat



the above is from wikipedia,
"jatman" wrote:

i am making an employee form, and one cell must validate some information.
it can be manually, but if i can get excel to do it, it would be better. i'm
using office 07.

Cell E15 will have a 9 digit number (ex. 044-096-857)
the formula would validate this number and accept it or reject it (in our
case, this would be a valid number)
The formula would rewrite this number but needs to double every second
number. The rewritten number would be 08409128107. if you add up all of
these individual numbers, you get 40 - a multiple of 10, so the number is
valid and 044-096-857 would be validated and accepted in cell E15.

if E15 had 856-247-963, the formula would rewrite that to 810644149123.
this rewritten number adds up to 43 (not a multiple of 10, so an error would
be returned indicating invalid data.

any suggestions.

jat

validate data using formulae
 

Here is a formula that (I am pretty sure) does what you want...

=IF(MOD(SUMPRODUCT((MOD(ROW($1:$9),2)=1)*MID(SUBST ITUTE(E15,"-",""),ROW($1:$9),1))+SUMPRODUCT((MOD(ROW($1:$9),2) =0)*(LEFT(TEXT(2*MID(SUBSTITUTE(E15,"-",""),ROW($1:$9),1),"00"))+RIGHT(TEXT(2*MID(SUBSTI TUTE(E15,"-",""),ROW($1:$9),1),"00")))),10)=0,"Valid","No t
Valid")

This formula can be copied down or across as needed.

--
Rick (MVP - Excel)


"jatman" wrote in message
...
i am making an employee form, and one cell must validate some information.
it can be manually, but if i can get excel to do it, it would be better.
i'm
using office 07.

Cell E15 will have a 9 digit number (ex. 044-096-857)
the formula would validate this number and accept it or reject it (in our
case, this would be a valid number)
The formula would rewrite this number but needs to double every second
number. The rewritten number would be 08409128107. if you add up all of
these individual numbers, you get 40 - a multiple of 10, so the number is
valid and 044-096-857 would be validated and accepted in cell E15.

if E15 had 856-247-963, the formula would rewrite that to 810644149123.
this rewritten number adds up to 43 (not a multiple of 10, so an error
would
be returned indicating invalid data.

any suggestions.

jat

validate data using formulae
 

If, by any chance, you wanted a VB solution... press Alt+F11 to go into the
VB editor, click Insert/Module from the VB editor's Menu Bar and copy/paste
the following into the code window that opened up...

Function LuhnValidate(ByVal S As String) As String
Dim X As Long
Dim Total As Long
Dim TempS As String
S = Replace(S, "-", "")
For X = 1 To 9
If X Mod 2 = 0 Then
Total = Total + Left(Format(2 * Mid(S, X, 1), "00"), 1) + _
Right(Format(2 * Mid(S, X, 1), "00"), 1)
Else
Total = Total + Mid(S, X, 1)
End If
Next
If Total Mod 10 = 0 Then
LuhnValidate = "Valid"
Else
LuhnValidate = "Not Valid"
End If
End Function

Now, back in your worksheet, use this formula...

=LuhnValidate(E15)

This formula can be copied down or across as needed.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
Here is a formula that (I am pretty sure) does what you want...

=IF(MOD(SUMPRODUCT((MOD(ROW($1:$9),2)=1)*MID(SUBST ITUTE(E15,"-",""),ROW($1:$9),1))+SUMPRODUCT((MOD(ROW($1:$9),2) =0)*(LEFT(TEXT(2*MID(SUBSTITUTE(E15,"-",""),ROW($1:$9),1),"00"))+RIGHT(TEXT(2*MID(SUBSTI TUTE(E15,"-",""),ROW($1:$9),1),"00")))),10)=0,"Valid","No t
Valid")

This formula can be copied down or across as needed.

--
Rick (MVP - Excel)


"jatman" wrote in message
...
i am making an employee form, and one cell must validate some information.
it can be manually, but if i can get excel to do it, it would be better.
i'm
using office 07.

Cell E15 will have a 9 digit number (ex. 044-096-857)
the formula would validate this number and accept it or reject it (in our
case, this would be a valid number)
The formula would rewrite this number but needs to double every second
number. The rewritten number would be 08409128107. if you add up all of
these individual numbers, you get 40 - a multiple of 10, so the number is
valid and 044-096-857 would be validated and accepted in cell E15.

if E15 had 856-247-963, the formula would rewrite that to 810644149123.
this rewritten number adds up to 43 (not a multiple of 10, so an error
would
be returned indicating invalid data.

any suggestions.

jat

validate data using formulae (Luhn algorithm)
 

My interpretation of that Wiki article is different than yours.

E.g., number to check = 456

Article says you add the digits, doubling every second value.
Numbers to sum = 4 + 10 + 6
Result = 20
Result MOD 10 = 0 == valid

You seem to be saying
Numbers to sum = 4 + 1 + 0 + 6
Result = 11
Result MOD 10 = 1 == invalid

I'm not saying either method is right or wrong, but a solution for you
depends on which algorithm you want to apply.

jatman wrote:
the formulae is the Luhn Algorithm, and has many different variations. the
one thing that i know for sure is that i will have the number to validate in
E15. on wikipedia's site, i can download an excel format, but it shows a
table formula, something that i do not want. it also shows a vb code, but i
am not sure how i would integrate it into excel.

jat



the above is from wikipedia,
"jatman" wrote:

i am making an employee form, and one cell must validate some information.
it can be manually, but if i can get excel to do it, it would be better. i'm
using office 07.

Cell E15 will have a 9 digit number (ex. 044-096-857)
the formula would validate this number and accept it or reject it (in our
case, this would be a valid number)
The formula would rewrite this number but needs to double every second
number. The rewritten number would be 08409128107. if you add up all of
these individual numbers, you get 40 - a multiple of 10, so the number is
valid and 044-096-857 would be validated and accepted in cell E15.

if E15 had 856-247-963, the formula would rewrite that to 810644149123.
this rewritten number adds up to 43 (not a multiple of 10, so an error would
be returned indicating invalid data.

any suggestions.

jat