|
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 |
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 |
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 - |
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 - |
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 ? |
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. |
| All times are GMT +1. The time now is 10:00 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com