Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Combining formulas
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 83
Default Combining formulas
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+M ID(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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 62
Default Combining formulas
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+M ID(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

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 62
Default Combining formulas
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)


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

If A1 contains 8101940 then put this formula into B2:

Beginning of the formula:
=TEXT(A1,"000000000") & "-" & RIGHT(SUMPRODUCT(
MID(TEXT(A1,"000000000"),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+M ID(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

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 83
Default Combining formulas
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 -

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 62
Default Combining formulas
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 -

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 62
Default Combining formulas
perhaps you should replace {1,2,3,4,5,6,7,8,9}
with {1;2;3;4;5;6;7;8;9}



"Charabeuh" a écrit dans le message de groupe de
discussion : ...
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 ?



Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 83
Default Combining formulas
On Jul 10, 6:20*pm, "Charabeuh" wrote:
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 -- Hide quoted text -

- Show quoted text -

yes.


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
combining Formulas mndpy Excel Worksheet Functions 2 December 13th 07 08:52 PM
Combining Formulas Phil H[_2_] Excel Programming 2 September 14th 06 10:30 AM
Combining Two Formulas to One Jerkyboy via OfficeKB.com Excel Discussion (Misc queries) 1 August 17th 06 05:28 AM
Combining formulas ben simpson Excel Discussion (Misc queries) 3 March 17th 06 03:49 PM
Combining formulas jezzica85 Excel Discussion (Misc queries) 5 March 3rd 06 06:56 AM


All times are GMT +1. The time now is 08:58 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement