Combining formulas

G

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
 
C

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 :
6b483341-172b-48d6-bcad-7c0194b36af9@i19g2000pro.googlegroups.com...
 
C

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

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

==========================================================
 
Top