Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data/Validate/List | Excel Worksheet Functions | |||
Validate Data Message Box | Excel Discussion (Misc queries) | |||
validate data aginst xml schema? | Excel Discussion (Misc queries) | |||
How to validate copy/paste data | Excel Discussion (Misc queries) | |||
Validate cells have data | Excel Worksheet Functions |