Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to check if the value in one cell is correct or not
Hi I have a complex validation process at hand and I wonder if there is any
way I can do this with Excel: My data looks as follows A1 B1 C1 D1 Code Decription Value Multiple Yes/No 6460880 Base1 6460885 Base2 I need to fill the values for columns C and D using a validation formula for the number on Column A The validation for numbers goes like this - starting from the back of the number, all the digits of the number are added together. Every other number is multiplied by two, and if that makes it a two digit number, each digit is added onto the total separately (eg 8 * 2 = 16 = 1+6 = 7). The total must be a multiple of 10. So, for example for the Code 6460880 we get the following; 6460880 = 6 4 6 0 8 8 0 *1 *2 *1 *2 *1 *2 *1 = 6+ 8+ 6+0 +8 +7+0 =35 (which is not a multiple of 10 and therefore the value for C is £5 and for D is NO) But for the next value 6460885 = 6 4 6 0 8 8 5 *1 *2 *1 *2 *1 *2 *1 = 6+ 8+ 6+0 +8 +7+5 =40 (So the value for C is 40 and the value for D is YES as it is a multiple of 10) So the table will look as follows: A1 B1 C1 D1 Code Decription Value Multiple of 10 Yes/No 6460880 Base1 35 NO 6460885 Base2 40 YES I am using Offfice Excel 2003 Thank you for any advice you can provide |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to check if the value in one cell is correct or not
If the number in A2 is always 7 digits then this will give the sum-of-digits
times {1,2,1,2,1,2,1} =SUMPRODUCT(--(MID(A2,ROW(Sheet1!$A$1:$A$7),1)),{1;2;1;2;1;2;1}) But that ignores the complication of "each digit is added onto the total separately (eg 8 * 2 = 16 = 1+6 = 7)" If this answer is in C2, we can test for multiple of 10 with =IF(MOD(C2,10),"No","Yes") I might find time on Monday to write a UDF to include the complications mentioned above. Please revisit the newsgroup or email me direct (get my email from my website) If you are unfamiliar with UDF, you may wish to read one or more of these David McRitchie's site on "getting started" with VBA http://www.mvps.org/dmcritchie/excel/getstarted.htm Debra Dalgleish's "Adding Code to a Workbook" http://www.contextures.com:80/xlvba01.html Ron de Bruin's intro to macros: http://www.rondebruin.nl/code.htm Jon Peltier's site: http://peltiertech.com/WordPress/2008/03/09/how-to-use-someone-else's-macro/ (General, Regular and Standard modules all describe the same thing.) best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "RobertoB" wrote in message ... Hi I have a complex validation process at hand and I wonder if there is any way I can do this with Excel: My data looks as follows A1 B1 C1 D1 Code Decription Value Multiple Yes/No 6460880 Base1 6460885 Base2 I need to fill the values for columns C and D using a validation formula for the number on Column A The validation for numbers goes like this - starting from the back of the number, all the digits of the number are added together. Every other number is multiplied by two, and if that makes it a two digit number, each digit is added onto the total separately (eg 8 * 2 = 16 = 1+6 = 7). The total must be a multiple of 10. So, for example for the Code 6460880 we get the following; 6460880 = 6 4 6 0 8 8 0 *1 *2 *1 *2 *1 *2 *1 = 6+ 8+ 6+0 +8 +7+0 =35 (which is not a multiple of 10 and therefore the value for C is £5 and for D is NO) But for the next value 6460885 = 6 4 6 0 8 8 5 *1 *2 *1 *2 *1 *2 *1 = 6+ 8+ 6+0 +8 +7+5 =40 (So the value for C is 40 and the value for D is YES as it is a multiple of 10) So the table will look as follows: A1 B1 C1 D1 Code Decription Value Multiple of 10 Yes/No 6460880 Base1 35 NO 6460885 Base2 40 YES I am using Offfice Excel 2003 Thank you for any advice you can provide |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to check if the value in one cell is correct or not
sorry !
if the back of the number is 0 than the function should be; you could try this function: Public Function Valid(Sinput As String) Dim I, X, Xsum For I = 1 To Len(Sinput) X = (2 - (I Mod 2)) * Mid(Sinput, Len(Sinput)-I+1, 1) Xsum = Xsum + X \ 10 + (X Mod 10) Next I Valid = Xsum End Function "Charabeuh" a écrit dans le message de groupe de discussion : ... hello, I don'tknow where is the back of a number. I supposed it is 6 in 6460880. you could try this function: Public Function Valid(Sinput As String) Dim I, X, Xsum For I = 1 To Len(Sinput) X = (2 - (I Mod 2)) * Mid(Sinput, I, 1) Xsum = Xsum + X \ 10 + (X Mod 10) Next I Valid = Xsum End Function into C2 put the formula : =valid(A2) into D2 put the formula : =if( mod(C2,10) =)=0,"YES","NO") if the back of the number is 0 than replace For I = 1 To Len(Sinput) with For I = Len(Sinput) to 1 step -1 "RobertoB" a écrit dans le message de groupe de discussion : ... Hi I have a complex validation process at hand and I wonder if there is any way I can do this with Excel: My data looks as follows A1 B1 C1 D1 Code Decription Value Multiple Yes/No 6460880 Base1 6460885 Base2 I need to fill the values for columns C and D using a validation formula for the number on Column A The validation for numbers goes like this - starting from the back of the number, all the digits of the number are added together. Every other number is multiplied by two, and if that makes it a two digit number, each digit is added onto the total separately (eg 8 * 2 = 16 = 1+6 = 7). The total must be a multiple of 10. So, for example for the Code 6460880 we get the following; 6460880 = 6 4 6 0 8 8 0 *1 *2 *1 *2 *1 *2 *1 = 6+ 8+ 6+0 +8 +7+0 =35 (which is not a multiple of 10 and therefore the value for C is £5 and for D is NO) But for the next value 6460885 = 6 4 6 0 8 8 5 *1 *2 *1 *2 *1 *2 *1 = 6+ 8+ 6+0 +8 +7+5 =40 (So the value for C is 40 and the value for D is YES as it is a multiple of 10) So the table will look as follows: A1 B1 C1 D1 Code Decription Value Multiple of 10 Yes/No 6460880 Base1 35 NO 6460885 Base2 40 YES I am using Offfice Excel 2003 Thank you for any advice you can provide |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to check if the value in one cell is correct or not
If the number in A2 is always 7 digits then this will give the
sum-of-digits times {1,2,1,2,1,2,1} =SUMPRODUCT(--(MID(A2,ROW(Sheet1!$A$1:$A$7),1)),{1;2;1;2;1;2;1}) But that ignores the complication of "each digit is added onto the total separately (eg 8 * 2 = 16 = 1+6 = 7)" This modification to your formula handles the "complication" as you call it (again, for 7-digit numbers only)... =SUMPRODUCT(MOD(MID(A1,ROW($A$1:$A$7),1)*{1;2;1;2; 1;2;1},9)) Summing the digits of a number repeatedly in order to produce a single digit is called (in mathematical circles) "casting out nines" and it is equivalent to finding the modulus (what the MOD function returns) of the number with respect to 9. That is... SumOfDigits: =MOD(Number,9) So I changed your comma version of the SUMPRODUCT function to the multiplication version and then just applied the above MOD function call to the original value being presented to the SUMPRODUCT function. -- Rick (MVP - Excel) "Bernard Liengme" wrote in message ... If the number in A2 is always 7 digits then this will give the sum-of-digits times {1,2,1,2,1,2,1} =SUMPRODUCT(--(MID(A2,ROW(Sheet1!$A$1:$A$7),1)),{1;2;1;2;1;2;1}) But that ignores the complication of "each digit is added onto the total separately (eg 8 * 2 = 16 = 1+6 = 7)" If this answer is in C2, we can test for multiple of 10 with =IF(MOD(C2,10),"No","Yes") I might find time on Monday to write a UDF to include the complications mentioned above. Please revisit the newsgroup or email me direct (get my email from my website) If you are unfamiliar with UDF, you may wish to read one or more of these David McRitchie's site on "getting started" with VBA http://www.mvps.org/dmcritchie/excel/getstarted.htm Debra Dalgleish's "Adding Code to a Workbook" http://www.contextures.com:80/xlvba01.html Ron de Bruin's intro to macros: http://www.rondebruin.nl/code.htm Jon Peltier's site: http://peltiertech.com/WordPress/2008/03/09/how-to-use-someone-else's-macro/ (General, Regular and Standard modules all describe the same thing.) best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "RobertoB" wrote in message ... Hi I have a complex validation process at hand and I wonder if there is any way I can do this with Excel: My data looks as follows A1 B1 C1 D1 Code Decription Value Multiple Yes/No 6460880 Base1 6460885 Base2 I need to fill the values for columns C and D using a validation formula for the number on Column A The validation for numbers goes like this - starting from the back of the number, all the digits of the number are added together. Every other number is multiplied by two, and if that makes it a two digit number, each digit is added onto the total separately (eg 8 * 2 = 16 = 1+6 = 7). The total must be a multiple of 10. So, for example for the Code 6460880 we get the following; 6460880 = 6 4 6 0 8 8 0 *1 *2 *1 *2 *1 *2 *1 = 6+ 8+ 6+0 +8 +7+0 =35 (which is not a multiple of 10 and therefore the value for C is £5 and for D is NO) But for the next value 6460885 = 6 4 6 0 8 8 5 *1 *2 *1 *2 *1 *2 *1 = 6+ 8+ 6+0 +8 +7+5 =40 (So the value for C is 40 and the value for D is YES as it is a multiple of 10) So the table will look as follows: A1 B1 C1 D1 Code Decription Value Multiple of 10 Yes/No 6460880 Base1 35 NO 6460885 Base2 40 YES I am using Offfice Excel 2003 Thank you for any advice you can provide |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to check if the value in one cell is correct or not
SumOfDigits: =MOD(Number,9)
Just so someone doesn't misinterpret the above statement when reviewing the archives in the future, the above would more appropriately be described this way... RepeatedlySummingDigits: =MOD(Number,9) -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... If the number in A2 is always 7 digits then this will give the sum-of-digits times {1,2,1,2,1,2,1} =SUMPRODUCT(--(MID(A2,ROW(Sheet1!$A$1:$A$7),1)),{1;2;1;2;1;2;1}) But that ignores the complication of "each digit is added onto the total separately (eg 8 * 2 = 16 = 1+6 = 7)" This modification to your formula handles the "complication" as you call it (again, for 7-digit numbers only)... =SUMPRODUCT(MOD(MID(A1,ROW($A$1:$A$7),1)*{1;2;1;2; 1;2;1},9)) Summing the digits of a number repeatedly in order to produce a single digit is called (in mathematical circles) "casting out nines" and it is equivalent to finding the modulus (what the MOD function returns) of the number with respect to 9. That is... SumOfDigits: =MOD(Number,9) So I changed your comma version of the SUMPRODUCT function to the multiplication version and then just applied the above MOD function call to the original value being presented to the SUMPRODUCT function. -- Rick (MVP - Excel) "Bernard Liengme" wrote in message ... If the number in A2 is always 7 digits then this will give the sum-of-digits times {1,2,1,2,1,2,1} =SUMPRODUCT(--(MID(A2,ROW(Sheet1!$A$1:$A$7),1)),{1;2;1;2;1;2;1}) But that ignores the complication of "each digit is added onto the total separately (eg 8 * 2 = 16 = 1+6 = 7)" If this answer is in C2, we can test for multiple of 10 with =IF(MOD(C2,10),"No","Yes") I might find time on Monday to write a UDF to include the complications mentioned above. Please revisit the newsgroup or email me direct (get my email from my website) If you are unfamiliar with UDF, you may wish to read one or more of these David McRitchie's site on "getting started" with VBA http://www.mvps.org/dmcritchie/excel/getstarted.htm Debra Dalgleish's "Adding Code to a Workbook" http://www.contextures.com:80/xlvba01.html Ron de Bruin's intro to macros: http://www.rondebruin.nl/code.htm Jon Peltier's site: http://peltiertech.com/WordPress/2008/03/09/how-to-use-someone-else's-macro/ (General, Regular and Standard modules all describe the same thing.) best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "RobertoB" wrote in message ... Hi I have a complex validation process at hand and I wonder if there is any way I can do this with Excel: My data looks as follows A1 B1 C1 D1 Code Decription Value Multiple Yes/No 6460880 Base1 6460885 Base2 I need to fill the values for columns C and D using a validation formula for the number on Column A The validation for numbers goes like this - starting from the back of the number, all the digits of the number are added together. Every other number is multiplied by two, and if that makes it a two digit number, each digit is added onto the total separately (eg 8 * 2 = 16 = 1+6 = 7). The total must be a multiple of 10. So, for example for the Code 6460880 we get the following; 6460880 = 6 4 6 0 8 8 0 *1 *2 *1 *2 *1 *2 *1 = 6+ 8+ 6+0 +8 +7+0 =35 (which is not a multiple of 10 and therefore the value for C is £5 and for D is NO) But for the next value 6460885 = 6 4 6 0 8 8 5 *1 *2 *1 *2 *1 *2 *1 = 6+ 8+ 6+0 +8 +7+5 =40 (So the value for C is 40 and the value for D is YES as it is a multiple of 10) So the table will look as follows: A1 B1 C1 D1 Code Decription Value Multiple of 10 Yes/No 6460880 Base1 35 NO 6460885 Base2 40 YES I am using Offfice Excel 2003 Thank you for any advice you can provide |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to check if the value in one cell is correct or not
My modification to your formula does NOT work correctly if there is a 9 in
the value in an odd numbered position (counting from the back of the number) because casting out the nines for it reduces it value to 0 whereas it should remain a 9 because it is a single digit number that is *not* multiplied by 2. My SumOfDigits (or as I called it in my follow up posting, RepeatedlySummingDigits) statement is correct; however, it is being applied to all the digits as opposed to only those digits that get multiplied by 2. End result... do NOT use my modification. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... If the number in A2 is always 7 digits then this will give the sum-of-digits times {1,2,1,2,1,2,1} =SUMPRODUCT(--(MID(A2,ROW(Sheet1!$A$1:$A$7),1)),{1;2;1;2;1;2;1}) But that ignores the complication of "each digit is added onto the total separately (eg 8 * 2 = 16 = 1+6 = 7)" This modification to your formula handles the "complication" as you call it (again, for 7-digit numbers only)... =SUMPRODUCT(MOD(MID(A1,ROW($A$1:$A$7),1)*{1;2;1;2; 1;2;1},9)) Summing the digits of a number repeatedly in order to produce a single digit is called (in mathematical circles) "casting out nines" and it is equivalent to finding the modulus (what the MOD function returns) of the number with respect to 9. That is... SumOfDigits: =MOD(Number,9) So I changed your comma version of the SUMPRODUCT function to the multiplication version and then just applied the above MOD function call to the original value being presented to the SUMPRODUCT function. -- Rick (MVP - Excel) "Bernard Liengme" wrote in message ... If the number in A2 is always 7 digits then this will give the sum-of-digits times {1,2,1,2,1,2,1} =SUMPRODUCT(--(MID(A2,ROW(Sheet1!$A$1:$A$7),1)),{1;2;1;2;1;2;1}) But that ignores the complication of "each digit is added onto the total separately (eg 8 * 2 = 16 = 1+6 = 7)" If this answer is in C2, we can test for multiple of 10 with =IF(MOD(C2,10),"No","Yes") I might find time on Monday to write a UDF to include the complications mentioned above. Please revisit the newsgroup or email me direct (get my email from my website) If you are unfamiliar with UDF, you may wish to read one or more of these David McRitchie's site on "getting started" with VBA http://www.mvps.org/dmcritchie/excel/getstarted.htm Debra Dalgleish's "Adding Code to a Workbook" http://www.contextures.com:80/xlvba01.html Ron de Bruin's intro to macros: http://www.rondebruin.nl/code.htm Jon Peltier's site: http://peltiertech.com/WordPress/2008/03/09/how-to-use-someone-else's-macro/ (General, Regular and Standard modules all describe the same thing.) best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "RobertoB" wrote in message ... Hi I have a complex validation process at hand and I wonder if there is any way I can do this with Excel: My data looks as follows A1 B1 C1 D1 Code Decription Value Multiple Yes/No 6460880 Base1 6460885 Base2 I need to fill the values for columns C and D using a validation formula for the number on Column A The validation for numbers goes like this - starting from the back of the number, all the digits of the number are added together. Every other number is multiplied by two, and if that makes it a two digit number, each digit is added onto the total separately (eg 8 * 2 = 16 = 1+6 = 7). The total must be a multiple of 10. So, for example for the Code 6460880 we get the following; 6460880 = 6 4 6 0 8 8 0 *1 *2 *1 *2 *1 *2 *1 = 6+ 8+ 6+0 +8 +7+0 =35 (which is not a multiple of 10 and therefore the value for C is £5 and for D is NO) But for the next value 6460885 = 6 4 6 0 8 8 5 *1 *2 *1 *2 *1 *2 *1 = 6+ 8+ 6+0 +8 +7+5 =40 (So the value for C is 40 and the value for D is YES as it is a multiple of 10) So the table will look as follows: A1 B1 C1 D1 Code Decription Value Multiple of 10 Yes/No 6460880 Base1 35 NO 6460885 Base2 40 YES I am using Offfice Excel 2003 Thank you for any advice you can provide |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to check if the value in one cell is correct or not
Rick,
I am impressed! When I read your explanation (casting out nines) my old memory had a flash but I would never have got your formula without weeks of work. Thanks Bernard "Rick Rothstein" wrote in message ... If the number in A2 is always 7 digits then this will give the sum-of-digits times {1,2,1,2,1,2,1} =SUMPRODUCT(--(MID(A2,ROW(Sheet1!$A$1:$A$7),1)),{1;2;1;2;1;2;1}) But that ignores the complication of "each digit is added onto the total separately (eg 8 * 2 = 16 = 1+6 = 7)" This modification to your formula handles the "complication" as you call it (again, for 7-digit numbers only)... =SUMPRODUCT(MOD(MID(A1,ROW($A$1:$A$7),1)*{1;2;1;2; 1;2;1},9)) Summing the digits of a number repeatedly in order to produce a single digit is called (in mathematical circles) "casting out nines" and it is equivalent to finding the modulus (what the MOD function returns) of the number with respect to 9. That is... SumOfDigits: =MOD(Number,9) So I changed your comma version of the SUMPRODUCT function to the multiplication version and then just applied the above MOD function call to the original value being presented to the SUMPRODUCT function. -- Rick (MVP - Excel) "Bernard Liengme" wrote in message ... If the number in A2 is always 7 digits then this will give the sum-of-digits times {1,2,1,2,1,2,1} =SUMPRODUCT(--(MID(A2,ROW(Sheet1!$A$1:$A$7),1)),{1;2;1;2;1;2;1}) But that ignores the complication of "each digit is added onto the total separately (eg 8 * 2 = 16 = 1+6 = 7)" If this answer is in C2, we can test for multiple of 10 with =IF(MOD(C2,10),"No","Yes") I might find time on Monday to write a UDF to include the complications mentioned above. Please revisit the newsgroup or email me direct (get my email from my website) If you are unfamiliar with UDF, you may wish to read one or more of these David McRitchie's site on "getting started" with VBA http://www.mvps.org/dmcritchie/excel/getstarted.htm Debra Dalgleish's "Adding Code to a Workbook" http://www.contextures.com:80/xlvba01.html Ron de Bruin's intro to macros: http://www.rondebruin.nl/code.htm Jon Peltier's site: http://peltiertech.com/WordPress/2008/03/09/how-to-use-someone-else's-macro/ (General, Regular and Standard modules all describe the same thing.) best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "RobertoB" wrote in message ... Hi I have a complex validation process at hand and I wonder if there is any way I can do this with Excel: My data looks as follows A1 B1 C1 D1 Code Decription Value Multiple Yes/No 6460880 Base1 6460885 Base2 I need to fill the values for columns C and D using a validation formula for the number on Column A The validation for numbers goes like this - starting from the back of the number, all the digits of the number are added together. Every other number is multiplied by two, and if that makes it a two digit number, each digit is added onto the total separately (eg 8 * 2 = 16 = 1+6 = 7). The total must be a multiple of 10. So, for example for the Code 6460880 we get the following; 6460880 = 6 4 6 0 8 8 0 *1 *2 *1 *2 *1 *2 *1 = 6+ 8+ 6+0 +8 +7+0 =35 (which is not a multiple of 10 and therefore the value for C is £5 and for D is NO) But for the next value 6460885 = 6 4 6 0 8 8 5 *1 *2 *1 *2 *1 *2 *1 = 6+ 8+ 6+0 +8 +7+5 =40 (So the value for C is 40 and the value for D is YES as it is a multiple of 10) So the table will look as follows: A1 B1 C1 D1 Code Decription Value Multiple of 10 Yes/No 6460880 Base1 35 NO 6460885 Base2 40 YES I am using Offfice Excel 2003 Thank you for any advice you can provide |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to check if the value in one cell is correct or not
Don't be so impressed... unfortunately, there is a problem with the formula.
The "casting out nines" part is correct, but it gets applied to too many of the digits. If you have a 9 in a position that does not get multiplied by 2, it should remain a 9 for the addition... unfortunately, my modification to your formula makes it a 0 (that is what the MOD 9 does) because there is no filter within it as to whether the digit gets multiplied by 2 or not. -- Rick (MVP - Excel) "Bernard Liengme" wrote in message ... Rick, I am impressed! When I read your explanation (casting out nines) my old memory had a flash but I would never have got your formula without weeks of work. Thanks Bernard "Rick Rothstein" wrote in message ... If the number in A2 is always 7 digits then this will give the sum-of-digits times {1,2,1,2,1,2,1} =SUMPRODUCT(--(MID(A2,ROW(Sheet1!$A$1:$A$7),1)),{1;2;1;2;1;2;1}) But that ignores the complication of "each digit is added onto the total separately (eg 8 * 2 = 16 = 1+6 = 7)" This modification to your formula handles the "complication" as you call it (again, for 7-digit numbers only)... =SUMPRODUCT(MOD(MID(A1,ROW($A$1:$A$7),1)*{1;2;1;2; 1;2;1},9)) Summing the digits of a number repeatedly in order to produce a single digit is called (in mathematical circles) "casting out nines" and it is equivalent to finding the modulus (what the MOD function returns) of the number with respect to 9. That is... SumOfDigits: =MOD(Number,9) So I changed your comma version of the SUMPRODUCT function to the multiplication version and then just applied the above MOD function call to the original value being presented to the SUMPRODUCT function. -- Rick (MVP - Excel) "Bernard Liengme" wrote in message ... If the number in A2 is always 7 digits then this will give the sum-of-digits times {1,2,1,2,1,2,1} =SUMPRODUCT(--(MID(A2,ROW(Sheet1!$A$1:$A$7),1)),{1;2;1;2;1;2;1}) But that ignores the complication of "each digit is added onto the total separately (eg 8 * 2 = 16 = 1+6 = 7)" If this answer is in C2, we can test for multiple of 10 with =IF(MOD(C2,10),"No","Yes") I might find time on Monday to write a UDF to include the complications mentioned above. Please revisit the newsgroup or email me direct (get my email from my website) If you are unfamiliar with UDF, you may wish to read one or more of these David McRitchie's site on "getting started" with VBA http://www.mvps.org/dmcritchie/excel/getstarted.htm Debra Dalgleish's "Adding Code to a Workbook" http://www.contextures.com:80/xlvba01.html Ron de Bruin's intro to macros: http://www.rondebruin.nl/code.htm Jon Peltier's site: http://peltiertech.com/WordPress/2008/03/09/how-to-use-someone-else's-macro/ (General, Regular and Standard modules all describe the same thing.) best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "RobertoB" wrote in message ... Hi I have a complex validation process at hand and I wonder if there is any way I can do this with Excel: My data looks as follows A1 B1 C1 D1 Code Decription Value Multiple Yes/No 6460880 Base1 6460885 Base2 I need to fill the values for columns C and D using a validation formula for the number on Column A The validation for numbers goes like this - starting from the back of the number, all the digits of the number are added together. Every other number is multiplied by two, and if that makes it a two digit number, each digit is added onto the total separately (eg 8 * 2 = 16 = 1+6 = 7). The total must be a multiple of 10. So, for example for the Code 6460880 we get the following; 6460880 = 6 4 6 0 8 8 0 *1 *2 *1 *2 *1 *2 *1 = 6+ 8+ 6+0 +8 +7+0 =35 (which is not a multiple of 10 and therefore the value for C is £5 and for D is NO) But for the next value 6460885 = 6 4 6 0 8 8 5 *1 *2 *1 *2 *1 *2 *1 = 6+ 8+ 6+0 +8 +7+5 =40 (So the value for C is 40 and the value for D is YES as it is a multiple of 10) So the table will look as follows: A1 B1 C1 D1 Code Decription Value Multiple of 10 Yes/No 6460880 Base1 35 NO 6460885 Base2 40 YES I am using Offfice Excel 2003 Thank you for any advice you can provide |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to check if the value in one cell is correct or not
This formula seems to work correctly (again, for 7-digit numbers only)...
=SUMPRODUCT(9*(MID(A2,ROW($A$1:$A$7),1)="9")+MOD(( MID(A2,ROW($A$1:$A$7),1))*{1;2;1;2;1;2;1},9)) As a side note.. the relationship I gave earlier, namely... RepeatedlySummingDigits: =MOD(Number,9) is not entirely accurate as it will take a number whose digits total 9 and reduce it to 0 whereas "casting out nines" would stop the reduction at the single digit 9 and **not** reduce it further (to a value of 0). The actual mathematical relationship for "casting out nines" would appear to be this... RepeatedlySummingDigits: =9*(Number<0)*(MOD(Number,9)=0)+MOD(Number,9) -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Don't be so impressed... unfortunately, there is a problem with the formula. The "casting out nines" part is correct, but it gets applied to too many of the digits. If you have a 9 in a position that does not get multiplied by 2, it should remain a 9 for the addition... unfortunately, my modification to your formula makes it a 0 (that is what the MOD 9 does) because there is no filter within it as to whether the digit gets multiplied by 2 or not. -- Rick (MVP - Excel) "Bernard Liengme" wrote in message ... Rick, I am impressed! When I read your explanation (casting out nines) my old memory had a flash but I would never have got your formula without weeks of work. Thanks Bernard "Rick Rothstein" wrote in message ... If the number in A2 is always 7 digits then this will give the sum-of-digits times {1,2,1,2,1,2,1} =SUMPRODUCT(--(MID(A2,ROW(Sheet1!$A$1:$A$7),1)),{1;2;1;2;1;2;1}) But that ignores the complication of "each digit is added onto the total separately (eg 8 * 2 = 16 = 1+6 = 7)" This modification to your formula handles the "complication" as you call it (again, for 7-digit numbers only)... =SUMPRODUCT(MOD(MID(A1,ROW($A$1:$A$7),1)*{1;2;1;2; 1;2;1},9)) Summing the digits of a number repeatedly in order to produce a single digit is called (in mathematical circles) "casting out nines" and it is equivalent to finding the modulus (what the MOD function returns) of the number with respect to 9. That is... SumOfDigits: =MOD(Number,9) So I changed your comma version of the SUMPRODUCT function to the multiplication version and then just applied the above MOD function call to the original value being presented to the SUMPRODUCT function. -- Rick (MVP - Excel) "Bernard Liengme" wrote in message ... If the number in A2 is always 7 digits then this will give the sum-of-digits times {1,2,1,2,1,2,1} =SUMPRODUCT(--(MID(A2,ROW(Sheet1!$A$1:$A$7),1)),{1;2;1;2;1;2;1}) But that ignores the complication of "each digit is added onto the total separately (eg 8 * 2 = 16 = 1+6 = 7)" If this answer is in C2, we can test for multiple of 10 with =IF(MOD(C2,10),"No","Yes") I might find time on Monday to write a UDF to include the complications mentioned above. Please revisit the newsgroup or email me direct (get my email from my website) If you are unfamiliar with UDF, you may wish to read one or more of these David McRitchie's site on "getting started" with VBA http://www.mvps.org/dmcritchie/excel/getstarted.htm Debra Dalgleish's "Adding Code to a Workbook" http://www.contextures.com:80/xlvba01.html Ron de Bruin's intro to macros: http://www.rondebruin.nl/code.htm Jon Peltier's site: http://peltiertech.com/WordPress/2008/03/09/how-to-use-someone-else's-macro/ (General, Regular and Standard modules all describe the same thing.) best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "RobertoB" wrote in message ... Hi I have a complex validation process at hand and I wonder if there is any way I can do this with Excel: My data looks as follows A1 B1 C1 D1 Code Decription Value Multiple Yes/No 6460880 Base1 6460885 Base2 I need to fill the values for columns C and D using a validation formula for the number on Column A The validation for numbers goes like this - starting from the back of the number, all the digits of the number are added together. Every other number is multiplied by two, and if that makes it a two digit number, each digit is added onto the total separately (eg 8 * 2 = 16 = 1+6 = 7). The total must be a multiple of 10. So, for example for the Code 6460880 we get the following; 6460880 = 6 4 6 0 8 8 0 *1 *2 *1 *2 *1 *2 *1 = 6+ 8+ 6+0 +8 +7+0 =35 (which is not a multiple of 10 and therefore the value for C is £5 and for D is NO) But for the next value 6460885 = 6 4 6 0 8 8 5 *1 *2 *1 *2 *1 *2 *1 = 6+ 8+ 6+0 +8 +7+5 =40 (So the value for C is 40 and the value for D is YES as it is a multiple of 10) So the table will look as follows: A1 B1 C1 D1 Code Decription Value Multiple of 10 Yes/No 6460880 Base1 35 NO 6460885 Base2 40 YES I am using Offfice Excel 2003 Thank you for any advice you can provide |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to check if the value in one cell is correct or not
This formula seems to work correctly (again, for 7-digit numbers only),
handling the "complication" that you mentioned correctly... =SUMPRODUCT(9*(MID(A2,ROW($A$1:$A$7),1)="9")+MOD(( MID(A2,ROW($A$1:$A$7),1))*{1;2;1;2;1;2;1},9)) As a side note.. the relationship I gave earlier, namely... RepeatedlySummingDigits: =MOD(Number,9) is not entirely accurate as it will take a number whose digits total 9 and reduce it to 0 whereas "casting out nines" would stop the reduction at the single digit 9 and **not** reduce it further (to a value of 0). The actual mathematical relationship for "casting out nines" would appear to be this... RepeatedlySummingDigits: =9*(Number<0)*(MOD(Number,9)=0)+MOD(Number,9) -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... My modification to your formula does NOT work correctly if there is a 9 in the value in an odd numbered position (counting from the back of the number) because casting out the nines for it reduces it value to 0 whereas it should remain a 9 because it is a single digit number that is *not* multiplied by 2. My SumOfDigits (or as I called it in my follow up posting, RepeatedlySummingDigits) statement is correct; however, it is being applied to all the digits as opposed to only those digits that get multiplied by 2. End result... do NOT use my modification. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... If the number in A2 is always 7 digits then this will give the sum-of-digits times {1,2,1,2,1,2,1} =SUMPRODUCT(--(MID(A2,ROW(Sheet1!$A$1:$A$7),1)),{1;2;1;2;1;2;1}) But that ignores the complication of "each digit is added onto the total separately (eg 8 * 2 = 16 = 1+6 = 7)" This modification to your formula handles the "complication" as you call it (again, for 7-digit numbers only)... =SUMPRODUCT(MOD(MID(A1,ROW($A$1:$A$7),1)*{1;2;1;2; 1;2;1},9)) Summing the digits of a number repeatedly in order to produce a single digit is called (in mathematical circles) "casting out nines" and it is equivalent to finding the modulus (what the MOD function returns) of the number with respect to 9. That is... SumOfDigits: =MOD(Number,9) So I changed your comma version of the SUMPRODUCT function to the multiplication version and then just applied the above MOD function call to the original value being presented to the SUMPRODUCT function. -- Rick (MVP - Excel) "Bernard Liengme" wrote in message ... If the number in A2 is always 7 digits then this will give the sum-of-digits times {1,2,1,2,1,2,1} =SUMPRODUCT(--(MID(A2,ROW(Sheet1!$A$1:$A$7),1)),{1;2;1;2;1;2;1}) But that ignores the complication of "each digit is added onto the total separately (eg 8 * 2 = 16 = 1+6 = 7)" If this answer is in C2, we can test for multiple of 10 with =IF(MOD(C2,10),"No","Yes") I might find time on Monday to write a UDF to include the complications mentioned above. Please revisit the newsgroup or email me direct (get my email from my website) If you are unfamiliar with UDF, you may wish to read one or more of these David McRitchie's site on "getting started" with VBA http://www.mvps.org/dmcritchie/excel/getstarted.htm Debra Dalgleish's "Adding Code to a Workbook" http://www.contextures.com:80/xlvba01.html Ron de Bruin's intro to macros: http://www.rondebruin.nl/code.htm Jon Peltier's site: http://peltiertech.com/WordPress/2008/03/09/how-to-use-someone-else's-macro/ (General, Regular and Standard modules all describe the same thing.) best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "RobertoB" wrote in message ... Hi I have a complex validation process at hand and I wonder if there is any way I can do this with Excel: My data looks as follows A1 B1 C1 D1 Code Decription Value Multiple Yes/No 6460880 Base1 6460885 Base2 I need to fill the values for columns C and D using a validation formula for the number on Column A The validation for numbers goes like this - starting from the back of the number, all the digits of the number are added together. Every other number is multiplied by two, and if that makes it a two digit number, each digit is added onto the total separately (eg 8 * 2 = 16 = 1+6 = 7). The total must be a multiple of 10. So, for example for the Code 6460880 we get the following; 6460880 = 6 4 6 0 8 8 0 *1 *2 *1 *2 *1 *2 *1 = 6+ 8+ 6+0 +8 +7+0 =35 (which is not a multiple of 10 and therefore the value for C is £5 and for D is NO) But for the next value 6460885 = 6 4 6 0 8 8 5 *1 *2 *1 *2 *1 *2 *1 = 6+ 8+ 6+0 +8 +7+5 =40 (So the value for C is 40 and the value for D is YES as it is a multiple of 10) So the table will look as follows: A1 B1 C1 D1 Code Decription Value Multiple of 10 Yes/No 6460880 Base1 35 NO 6460885 Base2 40 YES I am using Offfice Excel 2003 Thank you for any advice you can provide |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to check if the value in one cell is correct or not
Here is a modification to your formula (including modifications from the
other discussions I posted) which will handle *any* size "number" that you can enter into an Excel cell (if the number is entered as text, then the limit is very large, but I didn't test to see how big the text number could be and still work) and which properly handles what you called "the complication"... =SUMPRODUCT(9*(MID(A1,ROW(INDIRECT("$A$1:$A$"&LEN( A1))),1)="9")+MOD((MID(A1,ROW(INDIRECT("$A$1:$A$"& LEN(A1))),1))*(1+(MOD(LEN(A1)-ROW(INDIRECT("A$1:A$"&LEN(A1))),2)=1)),9)) -- Rick (MVP - Excel) "Bernard Liengme" wrote in message ... If the number in A2 is always 7 digits then this will give the sum-of-digits times {1,2,1,2,1,2,1} =SUMPRODUCT(--(MID(A2,ROW(Sheet1!$A$1:$A$7),1)),{1;2;1;2;1;2;1}) But that ignores the complication of "each digit is added onto the total separately (eg 8 * 2 = 16 = 1+6 = 7)" If this answer is in C2, we can test for multiple of 10 with =IF(MOD(C2,10),"No","Yes") I might find time on Monday to write a UDF to include the complications mentioned above. Please revisit the newsgroup or email me direct (get my email from my website) If you are unfamiliar with UDF, you may wish to read one or more of these David McRitchie's site on "getting started" with VBA http://www.mvps.org/dmcritchie/excel/getstarted.htm Debra Dalgleish's "Adding Code to a Workbook" http://www.contextures.com:80/xlvba01.html Ron de Bruin's intro to macros: http://www.rondebruin.nl/code.htm Jon Peltier's site: http://peltiertech.com/WordPress/2008/03/09/how-to-use-someone-else's-macro/ (General, Regular and Standard modules all describe the same thing.) best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "RobertoB" wrote in message ... Hi I have a complex validation process at hand and I wonder if there is any way I can do this with Excel: My data looks as follows A1 B1 C1 D1 Code Decription Value Multiple Yes/No 6460880 Base1 6460885 Base2 I need to fill the values for columns C and D using a validation formula for the number on Column A The validation for numbers goes like this - starting from the back of the number, all the digits of the number are added together. Every other number is multiplied by two, and if that makes it a two digit number, each digit is added onto the total separately (eg 8 * 2 = 16 = 1+6 = 7). The total must be a multiple of 10. So, for example for the Code 6460880 we get the following; 6460880 = 6 4 6 0 8 8 0 *1 *2 *1 *2 *1 *2 *1 = 6+ 8+ 6+0 +8 +7+0 =35 (which is not a multiple of 10 and therefore the value for C is £5 and for D is NO) But for the next value 6460885 = 6 4 6 0 8 8 5 *1 *2 *1 *2 *1 *2 *1 = 6+ 8+ 6+0 +8 +7+5 =40 (So the value for C is 40 and the value for D is YES as it is a multiple of 10) So the table will look as follows: A1 B1 C1 D1 Code Decription Value Multiple of 10 Yes/No 6460880 Base1 35 NO 6460885 Base2 40 YES I am using Offfice Excel 2003 Thank you for any advice you can provide |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to check if the value in one cell is correct or not
I came up with this while showering
=SUMPRODUCT(MOD(MID(A1,ROW($A$1:$A$7),1)*{1;2;1;2; 1;2;1},9))+SUMPRODUCT(--(MID(A1,ROW($A$1:$A$7),1)*{1;0;1;0;1;0;1}=9))*9 Yours is shorter, Have a good night Bernard "Rick Rothstein" wrote in message ... This formula seems to work correctly (again, for 7-digit numbers only)... =SUMPRODUCT(9*(MID(A2,ROW($A$1:$A$7),1)="9")+MOD(( MID(A2,ROW($A$1:$A$7),1))*{1;2;1;2;1;2;1},9)) As a side note.. the relationship I gave earlier, namely... RepeatedlySummingDigits: =MOD(Number,9) is not entirely accurate as it will take a number whose digits total 9 and reduce it to 0 whereas "casting out nines" would stop the reduction at the single digit 9 and **not** reduce it further (to a value of 0). The actual mathematical relationship for "casting out nines" would appear to be this... RepeatedlySummingDigits: =9*(Number<0)*(MOD(Number,9)=0)+MOD(Number,9) -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Don't be so impressed... unfortunately, there is a problem with the formula. The "casting out nines" part is correct, but it gets applied to too many of the digits. If you have a 9 in a position that does not get multiplied by 2, it should remain a 9 for the addition... unfortunately, my modification to your formula makes it a 0 (that is what the MOD 9 does) because there is no filter within it as to whether the digit gets multiplied by 2 or not. -- Rick (MVP - Excel) "Bernard Liengme" wrote in message ... Rick, I am impressed! When I read your explanation (casting out nines) my old memory had a flash but I would never have got your formula without weeks of work. Thanks Bernard "Rick Rothstein" wrote in message ... If the number in A2 is always 7 digits then this will give the sum-of-digits times {1,2,1,2,1,2,1} =SUMPRODUCT(--(MID(A2,ROW(Sheet1!$A$1:$A$7),1)),{1;2;1;2;1;2;1}) But that ignores the complication of "each digit is added onto the total separately (eg 8 * 2 = 16 = 1+6 = 7)" This modification to your formula handles the "complication" as you call it (again, for 7-digit numbers only)... =SUMPRODUCT(MOD(MID(A1,ROW($A$1:$A$7),1)*{1;2;1;2; 1;2;1},9)) Summing the digits of a number repeatedly in order to produce a single digit is called (in mathematical circles) "casting out nines" and it is equivalent to finding the modulus (what the MOD function returns) of the number with respect to 9. That is... SumOfDigits: =MOD(Number,9) So I changed your comma version of the SUMPRODUCT function to the multiplication version and then just applied the above MOD function call to the original value being presented to the SUMPRODUCT function. -- Rick (MVP - Excel) "Bernard Liengme" wrote in message ... If the number in A2 is always 7 digits then this will give the sum-of-digits times {1,2,1,2,1,2,1} =SUMPRODUCT(--(MID(A2,ROW(Sheet1!$A$1:$A$7),1)),{1;2;1;2;1;2;1}) But that ignores the complication of "each digit is added onto the total separately (eg 8 * 2 = 16 = 1+6 = 7)" If this answer is in C2, we can test for multiple of 10 with =IF(MOD(C2,10),"No","Yes") I might find time on Monday to write a UDF to include the complications mentioned above. Please revisit the newsgroup or email me direct (get my email from my website) If you are unfamiliar with UDF, you may wish to read one or more of these David McRitchie's site on "getting started" with VBA http://www.mvps.org/dmcritchie/excel/getstarted.htm Debra Dalgleish's "Adding Code to a Workbook" http://www.contextures.com:80/xlvba01.html Ron de Bruin's intro to macros: http://www.rondebruin.nl/code.htm Jon Peltier's site: http://peltiertech.com/WordPress/2008/03/09/how-to-use-someone-else's-macro/ (General, Regular and Standard modules all describe the same thing.) best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "RobertoB" wrote in message ... Hi I have a complex validation process at hand and I wonder if there is any way I can do this with Excel: My data looks as follows A1 B1 C1 D1 Code Decription Value Multiple Yes/No 6460880 Base1 6460885 Base2 I need to fill the values for columns C and D using a validation formula for the number on Column A The validation for numbers goes like this - starting from the back of the number, all the digits of the number are added together. Every other number is multiplied by two, and if that makes it a two digit number, each digit is added onto the total separately (eg 8 * 2 = 16 = 1+6 = 7). The total must be a multiple of 10. So, for example for the Code 6460880 we get the following; 6460880 = 6 4 6 0 8 8 0 *1 *2 *1 *2 *1 *2 *1 = 6+ 8+ 6+0 +8 +7+0 =35 (which is not a multiple of 10 and therefore the value for C is £5 and for D is NO) But for the next value 6460885 = 6 4 6 0 8 8 5 *1 *2 *1 *2 *1 *2 *1 = 6+ 8+ 6+0 +8 +7+5 =40 (So the value for C is 40 and the value for D is YES as it is a multiple of 10) So the table will look as follows: A1 B1 C1 D1 Code Decription Value Multiple of 10 Yes/No 6460880 Base1 35 NO 6460885 Base2 40 YES I am using Offfice Excel 2003 Thank you for any advice you can provide |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dup check 2 columns - need it to delete correct one | Excel Worksheet Functions | |||
Check inputbox enter is in correct format | Excel Programming | |||
What's the correct format to check the name of a sheet? | Excel Programming | |||
How do I correct a cyclic redundancy check error | Excel Discussion (Misc queries) | |||
How to correct this formula and check if more issues are existing? | Excel Programming |