Calculate the value of Z

[Harshad]

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

 

[Stefi]

=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:

 

[Rick Rothstein]

=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)

You don't really need the SUM function...

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

 

[Rick Rothstein]

And I believe the formula for Z can be shortened to this...

=MOD(SUMPRODUCT((10-ROW(A1:A7))*MID(A1,ROW(A1:A7),1))+MID(B1,2,1)+2*MID(B1,1,1),10)

 

[Rick Rothstein]

And we can further shorten the formula for Z to this...

=MOD(SUMPRODUCT((10-ROW(A1:A9))*MID(A1&B1,ROW(A1:A9),1)),10)

 

[Rick Rothstein]

Under the assumption this formula may be copied down, we need to make some
of the references absolute...

=MOD(SUMPRODUCT((10-ROW(A$1:A$9))*MID(A1&B1,ROW(A$1:A$9),1)),10)

 

[Harshad]

Thank for quick reply,

Unfortunately both of your (u & stefi) is not working.

Regreds,
Harshad

 

[Harshad]

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

 

[Rick Rothstein]

Well, of course our formulas do not work... in your other posting (the one
after this one that I am responding to) you state that you **completely**
changed the set up from what you originally posted. How could our formulas
have possibly worked after you did that?

--
Rick (MVP - Excel)

Thank for quick reply,

Unfortunately both of your (u & stefi) is not working.

Regreds,
Harshad

 

[Rick Rothstein]

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)

 

[Harshad]

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*MID(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]

It's not working.

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!!!

 

[Harshad]

Dear Rick,

It's working excellent.

Thank you for extraordinary efforts.

With regards,
Harshad