Hello,

You found the way to correct the formula :
It is to maintain ROW(1:9) as a contant array.

1) the first way is to replace ROW(1:9)
with {1,2,3,4,5,6,7,8,9}

2) another way is to replace ROW(1:9)
with ROW($1:$9)

Does this correct the problem ?






"gcotterl" a écrit dans le message de groupe
de discussion :
...
Hello.

I made an error: My formulas are in row A (not B)

=======

A1 contains 8101940

B1 contains your formula:

=TEXT(A1,"000000000") & "-" &
RIGHT(SUMPRODUCT(MID(TEXT(A1,"000000000"),ROW(1:9) ,1)
*{1;3;7;9;1;3;7;9;1}))

and 008101940-7 is displayed (THIS IS CORRECT).

========

B2 contains 209051010

But when I copy and paste your formula into B2, the formula changes
to::
:
=TEXT(A1,"000000000") & "-" &
RIGHT(SUMPRODUCT(MID(TEXT(A1,"000000000"),ROW(2:10 ),1)
*{1;3;7;9;1;3;7;9;1}))

and #VALUE! is displayed (instead of 209051010-2)

========

The only differences are in the "ROW" expressions:

In B1: it is: ROW(1:9),1)
In B2, it is: ROW(2:10),1)

How should I resolve this problem?

Gary

================================================== ========


On Jul 10, 5:18 pm, "Charabeuh" wrote:
Hello,

If A1 contains 8101940 then put this formula into B2:

Beginning of the formula:
=TEXT(A1,"000000000") & "-" & RIGHT(SUMPRODUCT(
MID(TEXT(A1,"000000000"),ROW(1:9),1) * {1,3,7,9,1,3,7,9,1}))
End of the formula

If it does not work, try replacing {1,3,7,9,1,3,7,9,1} with
{1;3;7;9;1;3;7;9;1}

Hope this will help you.

(excel10+win7)

"gcotterl" a écrit dans le message de
groupe
de discussion :
...



A1 contains:

8101940

=======================

B1 contains this formula:

=TEXT(A2,"000000000")

=======================

C1 contains this formula:

=MID(B2,1,1)*1+MID(B2,2,1)*3+MID(B2,3,1)*7+MID(B2, 4,1)*9+MID(B2,5,1)*1+MID(*B2,6,1)*3+MID(B2,7,1)*7+ MID(B2,8,1)*9+MID(B2,9,1)*1

======================

D1 contains this formula:

=RIGHT(C2,1)

======================

E1 contains this formula:

=B2&"-"&D2

which displays:

008101940-7

=============================================

How can I combine the 4 formulas so B2 contains the combination of the
4 formulas and diplays:

008101940-7- Hide quoted text -

- Show quoted text -