Combining formulas

[gcotterl]

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

 

[Charabeuh]

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" <[email protected]> a écrit dans le message de groupe
de discussion :
(e-mail address removed)...

 

[Charabeuh]

To copy the formula into others cells than B2 (ex: if you want to drag the
formula down your column B) it is better to replace
ROW(1:9) by ROW($1:$9)

 

[gcotterl]

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

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

 

[Charabeuh]

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" <[email protected]> a écrit dans le message de groupe
de discussion :
(e-mail address removed)...

 

[Charabeuh]

perhaps you should replace {1,2,3,4,5,6,7,8,9}
with {1;2;3;4;5;6;7;8;9}

 

[gcotterl]

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" <[email protected]> a écrit dans le message de groupe
de discussion :
(e-mail address removed)...













- Show quoted text -

yes.