R
RobertoB
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
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