Dear Rick,

It's working excellent.

Thank you for extraordinary efforts.

With regards,
Harshad

"Rick Rothstein" wrote:

It's not working.
a little change in my equation:
ABCDEFG-HI-Z, all are independent numerals
A to G are in cell A1, then - (des) HI also in cell A1, and Z in cell B1
Some times the number like, 64-19
Means each time it will not nine digit number, Eg 50-00-Z, 100-01-Z,
1116-70-Z, 17095-24-Z, 101316-73-Z, 1019345-04-Z (the last one is
maximum (seven-two-Z)digits)

My mean is to say that Stefi & your formula (all previous except last
formula) are works fine only if the Cell A1 contain seven digit number and
B1
is of two digit no.

If i have 1459876 in Cell A1 and 63 in cell B1, the formula in this post
yields,

But you just said (see the first part of the above quoted section from your
postings) that you do NOT have 145986 in A1 and 63 in B1... instead, you
said you have 145986-63 **all** in A1!!!

6) Your 5th & last formula
=MOD(SUMPRODUCT((LEN(A1)-ROW(INDIRECT("A$1:A$"&(LEN(A1)-1))))*MID(SUBSTITUTE(A1,"-",""),ROW(INDIRECT("A$1:A$"&(LEN(A1)-1))),1)),10),
yields 6, this is wrong.

Of course it does not work if part of your number is in A1 and part is in
B1... this formula was meant to handle the case when **all** your numbers
are in A1 with a dash separating them LIKE YOU SAID YOU HAVE in the first
part of the above quoted section from **your** posting!!!

--
Rick (MVP - Excel)


"Harshad" wrote in message
...
Dear Rick,

Thank you for kind guidance.
My mean is to say that Stefi & your formula (all previous except last
formula) are works fine only if the Cell A1 contain seven digit number and
B1
is of two digit no.

If i have 1459876 in Cell A1 and 63 in cell B1, the formula in this post
yields,

1) Stefi's formula
=MOD(SUM(MID(A1,1,1)*9,MID(A1,2,1)*8,MID(A1,3,1)*7 ,MID(A1,4,1)*6,MID(A1,5,1)*5,MID(A1,6,1)*4,MID(A1, 7,1)*3,MID(B1,1,1)*2,MID(B1,2,1)*1),10)
Yields 1, which is right.

2) Your 1st formula
=MOD(SUM(MID(A1,1,1)*9,MID(A1,2,1)*8,MID(A1,3,1)*7 ,MID(A1,4,1)*6,MID(A1,5,1)*5,MID(A1,6,1)*4,MID(A1, 7,1)*3,MID(B1,1,1)*2,MID(B1,2,1)*1),10),
yields 1, this is also right.

3) Your 2nd formula
=MOD(SUMPRODUCT((10-ROW(A1:A7))*MID(A1,ROW(A1:A7),1))+MID(B1,2,1)+2*MI D(B1,1,1),10),
yields 1, this is also right.

4) Your 3rd formula
=MOD(SUMPRODUCT((10-ROW(A1:A9))*MID(A1&B1,ROW(A1:A9),1)),10), yields 1,
this
is also right.

5) Your 4th formula
=MOD(SUMPRODUCT((10-ROW(A$1:A$9))*MID(A1&B1,ROW(A$1:A$9),1)),10), yields
1,
this is also right.

6) Your 5th & last formula
=MOD(SUMPRODUCT((LEN(A1)-ROW(INDIRECT("A$1:A$"&(LEN(A1)-1))))*MID(SUBSTITUTE(A1,"-",""),ROW(INDIRECT("A$1:A$"&(LEN(A1)-1))),1)),10),
yields 6, this is wrong.

But when i put 1459 in cell A1 and 63 in cell B1, all the formula results
#VALUE!. (Any no in cell A1, if less than seven digit and any no in cell
B1,
if less than two digit, shows result #VALUE!. These occurs in all formula
except last). In last formula there is no indication of cell B1, hence no
effect of cell B1. And also wrong answer.

Please rethink,i have one to seven digit number cell A1 and one/two digit
number cell B1.

Harshad

"Rick Rothstein" wrote:

Just so you know, Stefi's formula, as well as the one I posted, work just
fine... against the set-up you told us you had in your original posting
(ABCDEFG in A1, HI in B1). Your "little change" is, in fact, quite a
large
change indeed. Not only have you put all you digits in the A1 and
separated
them with a dash, BUT NOW you tell us the number of digits can vary as
well.
These were not "little changes" by any means. Now, assuming you don't
change
your setup again from what you have just told us it is, this formula
should
do what you want...

=MOD(SUMPRODUCT((LEN(A1)-ROW(INDIRECT("A$1:A$"&(LEN(A1)-1))))*MID(SUBSTITUTE(A1,"-",""),ROW(INDIRECT("A$1:A$"&(LEN(A1)-1))),1)),10)

--
Rick (MVP - Excel)


"Harshad" wrote in message
...
Dear Stefi,

It's not working.
a little change in my equation:
ABCDEFG-HI-Z, all are independent numerals
A to G are in cell A1, then - (des) HI also in cell A1, and Z in cell
B1
Some times the number like, 64-19
Means each time it will not nine digit number, Eg 50-00-Z, 100-01-Z,
1116-70-Z, 17095-24-Z, 101316-73-Z, 1019345-04-Z (the last one is
maximum
(seven-two-Z)digits)

Hope this time you will get sucess.

Harshad

"Stefi" wrote:

=MOD(SUM(MID(A1,1,1)*9,MID(A1,2,1)*8,MID(A1,3,1)*7 ,MID(A1,4,1)*6,MID(A1,5,1)*5,MID(A1,6,1)*4,MID(A1, 7,1)*3,MID(B1,1,1)*2,MID(B1,2,1)*1),10)

Check your example, it doesn't suit the formula!

Regards,
Stefi

€žHarshad€� ezt Ã*rta:

My equation is ABCDEFG-HI-Z, all alphabets (A,B,C,D,E,F,G,H,I) have
value
between 0-9.

I have to calculate the value of Z, as follows.

Value of Z is = {( I*1) + (H*2 ) + (G*3 ) + (F*4 ) + (E*5 ) + (D*6 )
+
(C*7
) + (B*8 ) + (A*9 )}/ 10

= Q + Z/10, where Q and Z represents integer and remainder
respectively

For eg. 1034526-98-Z.
Z can be calculated as,

= {( 9*1) + (8*0 ) + (7*3 ) + (6*4 ) + (5*5 ) + (4*2 ) + (3*6 ) +
(2*9 ) +
(1*8 )}/ 10

= {9 + 0 + 21 + 24 + 25 + 8 + 18 + 18 + 8}/10

= 131/10

= 13 + 1/10

So, Z = 1.

In excel, if ABCDEFG numerals are in cell A1, HI numerals are in
cell
B1 and
Z is in cell C1. I want formula, which gives the value of Z between
0-9.

Thank in advance.

Harshad