Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiplying non consecutive rows, than adding it.
Hello,
I need your help here. I am trying to calculate a monthly progress of planned vs. real. My "planned" formula is ok. The problem comes for the "real" one. Let's say I wanted to multiply the odd rows from D2:D63 * even rows from B2:B63, then add the results. I have tried things like =SUMPRODUCT((IF(MOD(ROW(D2:D63),2)=1,D2:D63,0)*IF( MOD(ROW(B2:B63),2)=0,B2:B63,0))) but it won't work, it will always display 0 as a result. Odd rows from B2:B63 will be blank, and in D2:D63 we will have the "real" progress on odd rows and "planned" progress on even rows. I know it shouldn't be so difficult, but I'm stuck. Thanks in advance. Regards, RF |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiplying non consecutive rows, than adding it.
try
=SUMPRODUCT((MOD(ROW(D3:D64),2)=1)*D3:D64*(MOD(ROW (B2:B63),2)=0)*B2:B63) "RF" wrote: Hello, I need your help here. I am trying to calculate a monthly progress of planned vs. real. My "planned" formula is ok. The problem comes for the "real" one. Let's say I wanted to multiply the odd rows from D2:D63 * even rows from B2:B63, then add the results. I have tried things like =SUMPRODUCT((IF(MOD(ROW(D2:D63),2)=1,D2:D63,0)*IF( MOD(ROW(B2:B63),2)=0,B2:B63,0))) but it won't work, it will always display 0 as a result. Odd rows from B2:B63 will be blank, and in D2:D63 we will have the "real" progress on odd rows and "planned" progress on even rows. I know it shouldn't be so difficult, but I'm stuck. Thanks in advance. Regards, RF |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiplying non consecutive rows, than adding it.
I need your help here. I am trying to calculate a monthly progress of
planned vs. real. My "planned" formula is ok. The problem comes for the "real" one. Let's say I wanted to multiply the odd rows from D2:D63 * even rows from B2:B63, then add the results. I have tried things like =SUMPRODUCT((IF(MOD(ROW(D2:D63),2)=1,D2:D63,0)*IF( MOD(ROW(B2:B63),2)=0,B2:B63,0))) but it won't work, it will always display 0 as a result. Odd rows from B2:B63 will be blank, and in D2:D63 we will have the "real" progress on odd rows and "planned" progress on even rows. Does this do what you are looking for? =SUMPRODUCT((B2:B62)*(D3:D63)) assuming the match up for multiplication is the odd row from the "D" column times the previous row in the "B" column. Rick |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiplying non consecutive rows, than adding it.
WOW! That's it!
Thanks a LOT Toppers. Now, if it wouldn't be much trouble... what was I doing wrong? Regards, RF "Toppers" wrote: try =SUMPRODUCT((MOD(ROW(D3:D64),2)=1)*D3:D64*(MOD(ROW (B2:B63),2)=0)*B2:B63) "RF" wrote: Hello, I need your help here. I am trying to calculate a monthly progress of planned vs. real. My "planned" formula is ok. The problem comes for the "real" one. Let's say I wanted to multiply the odd rows from D2:D63 * even rows from B2:B63, then add the results. I have tried things like =SUMPRODUCT((IF(MOD(ROW(D2:D63),2)=1,D2:D63,0)*IF( MOD(ROW(B2:B63),2)=0,B2:B63,0))) but it won't work, it will always display 0 as a result. Odd rows from B2:B63 will be blank, and in D2:D63 we will have the "real" progress on odd rows and "planned" progress on even rows. I know it shouldn't be so difficult, but I'm stuck. Thanks in advance. Regards, RF |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiplying non consecutive rows, than adding it.
YAY!
This one works as well... I don't know why did I try building such a long formula if a simple one like this does the job. As one of my orevious teachers would say: "it's the elegant solution". Thanks Rick. Regards, RF "Rick Rothstein (MVP - VB)" wrote: I need your help here. I am trying to calculate a monthly progress of planned vs. real. My "planned" formula is ok. The problem comes for the "real" one. Let's say I wanted to multiply the odd rows from D2:D63 * even rows from B2:B63, then add the results. I have tried things like =SUMPRODUCT((IF(MOD(ROW(D2:D63),2)=1,D2:D63,0)*IF( MOD(ROW(B2:B63),2)=0,B2:B63,0))) but it won't work, it will always display 0 as a result. Odd rows from B2:B63 will be blank, and in D2:D63 we will have the "real" progress on odd rows and "planned" progress on even rows. Does this do what you are looking for? =SUMPRODUCT((B2:B62)*(D3:D63)) assuming the match up for multiplication is the odd row from the "D" column times the previous row in the "B" column. Rick |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiplying non consecutive rows, than adding it.
Wait a minute... I am not so sure it does what you want anymore.
You said earlier that Toppers' solution worked for you. My formula does not produce the same results as his. Which one produces the correct result for you... Toppers' or mine? I can produce the same result as Toppers' formula with a slight modification to the one I posted earlier... =SUMPRODUCT((B2:B62)*(D3:D63)*MOD(ROW(D3:D63),2)) What it does (and what Topper's does)(and perhaps what you asked for when you said "non-consecutive") is multiply only the odd rows in "D" by the even rows in "B". That is decidedly different than what my original formula did, which was to multiply EVERY row's value in "D" by the previous row's value in "B". I am not entirely sure what you actually were after, but you should study the results from my two formulas, and Toppers' formula, to make sure you are getting the result you really want. Rick This one works as well... I don't know why did I try building such a long formula if a simple one like this does the job. As one of my orevious teachers would say: "it's the elegant solution". Thanks Rick. Regards, RF "Rick Rothstein (MVP - VB)" wrote: I need your help here. I am trying to calculate a monthly progress of planned vs. real. My "planned" formula is ok. The problem comes for the "real" one. Let's say I wanted to multiply the odd rows from D2:D63 * even rows from B2:B63, then add the results. I have tried things like =SUMPRODUCT((IF(MOD(ROW(D2:D63),2)=1,D2:D63,0)*IF( MOD(ROW(B2:B63),2)=0,B2:B63,0))) but it won't work, it will always display 0 as a result. Odd rows from B2:B63 will be blank, and in D2:D63 we will have the "real" progress on odd rows and "planned" progress on even rows. Does this do what you are looking for? =SUMPRODUCT((B2:B62)*(D3:D63)) assuming the match up for multiplication is the odd row from the "D" column times the previous row in the "B" column. Rick |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiplying non consecutive rows, than adding it.
Rick,
one of the drawbacks with your earlier formula (and with mine in another thread) is that if the supposedly blank cells in column B actually contained a space (or something else which made them look blank), then the formula will return #VALUE as we are trying to multiply text. I think Topper's solution (and your modification here) is the safest approach. Pete On Jul 4, 9:17 pm, "Rick Rothstein \(MVP - VB\)" wrote: Wait a minute... I am not so sure it does what you want anymore. You said earlier that Toppers' solution worked for you. My formula does not produce the same results as his. Which one produces the correct result for you... Toppers' or mine? I can produce the same result as Toppers' formula with a slight modification to the one I posted earlier... =SUMPRODUCT((B2:B62)*(D3:D63)*MOD(ROW(D3:D63),2)) What it does (and what Topper's does)(and perhaps what you asked for when you said "non-consecutive") is multiply only the odd rows in "D" by the even rows in "B". That is decidedly different than what my original formula did, which was to multiply EVERY row's value in "D" by the previous row's value in "B". I am not entirely sure what you actually were after, but you should study the results from my two formulas, and Toppers' formula, to make sure you are getting the result you really want. Rick This one works as well... I don't know why did I try building such a long formula if a simple one like this does the job. As one of my orevious teachers would say: "it's the elegant solution". Thanks Rick. Regards, RF "Rick Rothstein (MVP - VB)" wrote: I need your help here. I am trying to calculate a monthly progress of planned vs. real. My "planned" formula is ok. The problem comes for the "real" one. Let's say I wanted to multiply the odd rows from D2:D63 * even rows from B2:B63, then add the results. I have tried things like =SUMPRODUCT((IF(MOD(ROW(D2:D63),2)=1,D2:D63,0)*IF( MOD(ROW(B2:B63),2)=0,B2:B*63,0))) but it won't work, it will always display 0 as a result. Odd rows from B2:B63 will be blank, and in D2:D63 we will have the "real" progress on odd rows and "planned" progress on even rows. Does this do what you are looking for? =SUMPRODUCT((B2:B62)*(D3:D63)) assuming the match up for multiplication is the odd row from the "D" column times the previous row in the "B" column. Rick- Hide quoted text - - Show quoted text - |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiplying non consecutive rows, than adding it.
one of the drawbacks with your earlier formula (and with mine in
another thread) is that if the supposedly blank cells in column B actually contained a space (or something else which made them look blank), then the formula will return #VALUE as we are trying to multiply text. Notice in my first formula... =SUMPRODUCT((B2:B62)*(D3:D63)) the ranges are offset from each other... theoretically, according to the original data range, these offset ranges are never over a "blank" cell. I think Topper's solution (and your modification here) is the safest approach. But what is it that the RF wanted? Remember, my original formula produces a completely different result than either Toppers' or my modification to produce the same result as Toppers'... and yet the RF said both solved his problem. Since that is not possible, I am left wondering what was really needed. Rick |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiplying non consecutive rows, than adding it.
Hi Rick,
Well surprisingly, and maybe because of particular conditions on my sheet, Topper's and yours both return the same output. I tried the other one you suggest and it works too. I will change the values to study their reactions. What I definitely need the formula to do is multiply every odd row in D2:D63 * every even row in B2:B63, then add the results. I think it is almost the same as what you stated earlier: assuming the match up for multiplication is the odd row from the "D" column times the previous row in the "B" column. To clarify, I need: =D3*B2+D5*B4+D7*B6+.... Thanks. RF "Rick Rothstein (MVP - VB)" wrote: Wait a minute... I am not so sure it does what you want anymore. You said earlier that Toppers' solution worked for you. My formula does not produce the same results as his. Which one produces the correct result for you... Toppers' or mine? I can produce the same result as Toppers' formula with a slight modification to the one I posted earlier... =SUMPRODUCT((B2:B62)*(D3:D63)*MOD(ROW(D3:D63),2)) What it does (and what Topper's does)(and perhaps what you asked for when you said "non-consecutive") is multiply only the odd rows in "D" by the even rows in "B". That is decidedly different than what my original formula did, which was to multiply EVERY row's value in "D" by the previous row's value in "B". I am not entirely sure what you actually were after, but you should study the results from my two formulas, and Toppers' formula, to make sure you are getting the result you really want. Rick This one works as well... I don't know why did I try building such a long formula if a simple one like this does the job. As one of my orevious teachers would say: "it's the elegant solution". Thanks Rick. Regards, RF "Rick Rothstein (MVP - VB)" wrote: I need your help here. I am trying to calculate a monthly progress of planned vs. real. My "planned" formula is ok. The problem comes for the "real" one. Let's say I wanted to multiply the odd rows from D2:D63 * even rows from B2:B63, then add the results. I have tried things like =SUMPRODUCT((IF(MOD(ROW(D2:D63),2)=1,D2:D63,0)*IF( MOD(ROW(B2:B63),2)=0,B2:B63,0))) but it won't work, it will always display 0 as a result. Odd rows from B2:B63 will be blank, and in D2:D63 we will have the "real" progress on odd rows and "planned" progress on even rows. Does this do what you are looking for? =SUMPRODUCT((B2:B62)*(D3:D63)) assuming the match up for multiplication is the odd row from the "D" column times the previous row in the "B" column. Rick |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiplying non consecutive rows, than adding it.
Well surprisingly, and maybe because of particular conditions on my sheet,
Topper's and yours both return the same output. I tried the other one you suggest and it works too. I will change the values to study their reactions. What I definitely need the formula to do is multiply every odd row in D2:D63 * every even row in B2:B63, then add the results. I think it is almost the same as what you stated earlier: assuming the match up for multiplication is the odd row from the "D" column times the previous row in the "B" column. To clarify, I need: =D3*B2+D5*B4+D7*B6+.... Then I am pretty sure my original formula does not work for you. Here is the test data I used... COL == B D ROW ===== 2 2 12 3 3 33 4 4 32 5 5 12 6 6 34 7 7 52 8 8 23 9 9 42 Hopefully, the layout for the data is readable. Here are the results I get for the three formulas... My 1st Formula - 1189 My 2nd Formula - 762 Toppers' Formula - 762 My first formula does NOT produce the result you are looking for. Rick |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiplying non consecutive rows, than adding it.
Rick,
even though your ranges are offset from each other, your first formula will produce B2*D3 + B3*D4 + B4*D5 etc. Now, B3*D4 should equal zero as B3 should be blank, but what I pointed out earlier is that if B3 contains a space you will get #VALUE. The formula can work with the OP's data if he has alternate blanks in column B. In your test data you do not have alternate blanks, so you are adding up too many values. Here's an alternative array* formula which is "safe" from spurious blanks: =SUM(IF(MOD(ROW(B2:B62),2)=0,(D3:D63)*(B2:B62))) *Commit with CSE. Pete On Jul 4, 10:04 pm, "Rick Rothstein \(MVP - VB\)" wrote: Well surprisingly, and maybe because of particular conditions on my sheet, Topper's and yours both return the same output. I tried the other one you suggest and it works too. I will change the values to study their reactions. What I definitely need the formula to do is multiply every odd row in D2:D63 * every even row in B2:B63, then add the results. I think it is almost the same as what you stated earlier: assuming the match up for multiplication is the odd row from the "D" column times the previous row in the "B" column. To clarify, I need: =D3*B2+D5*B4+D7*B6+.... Then I am pretty sure my original formula does not work for you. Here is the test data I used... COL == B D ROW ===== 2 2 12 3 3 33 4 4 32 5 5 12 6 6 34 7 7 52 8 8 23 9 9 42 Hopefully, the layout for the data is readable. Here are the results I get for the three formulas... My 1st Formula - 1189 My 2nd Formula - 762 Toppers' Formula - 762 My first formula does NOT produce the result you are looking for. Rick |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiplying non consecutive rows, than adding it.
even though your ranges are offset from each other, your first formula
will produce B2*D3 + B3*D4 + B4*D5 etc. Now, B3*D4 should equal zero as B3 should be blank, but what I pointed out earlier is that if B3 contains a space you will get #VALUE. I assumed since the OP wanted to reach back to B2 (an even row) from D3 (an odd row), that he would have data in these cells although I would expect D2 to probably be blank (sort of like a starting condition). The formula can work with the OP's data if he has alternate blanks in column B. In your test data you do not have alternate blanks, so you are adding up too many values. Ahhh! (The light-bulb goes on<g)... **that** is why my first equation worked... there are alternating blanks in each row!!! Rick |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiplying non consecutive rows, than adding it.
Hmm! I have been answering your questions here in this group while you are
running it over in m.p.e.worksheet.functions also. Something for you to consider for the future... From a post by Jeff Johnson: "You have posted this question individually to multiple groups. This is called Multiposting and it's BAD. Replies made in one group will not be visible in the other groups, which may cause multiple people to respond to your question with the same answer because they didn't know someone else had already done it. This is a waste of time. If you MUST post your message to multiple groups, post a single message and select all the groups (or type their names manually in the Newsgroups field, separated by commas) in which you want it to be seen. This is called Crossposting and when used properly it is GOOD." Some additional comment previously posted by me: "You may not see this as a problem, but those of us who volunteer answering questions on newsgroups do see it as a problem. You can't imagine how annoying it is for a volunteer to read a question, research background material, test sample code and then formulate and post an answer to the original question only to go to another newsgroup and find the question posted and ALREADY answered over there. On top of that, if you cross-post your question, all of the readers in all the newsgroups it is cross-posted to get to see both the original question and all of the answers given to it. This is beneficial to you because then we can add additional material to, add clarification to, as well as add additional examples to an answer you have received previously... that means you end up with a more complete solution to your problem. This is a win-win situation for all of us." Rick |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiplying non consecutive rows, than adding it.
Hi Rick,
Roger that. It won't happen again. My apologies for any caused trouble. Thanks again for your kind and valuable help. Regards, RF "Rick Rothstein (MVP - VB)" wrote: Hmm! I have been answering your questions here in this group while you are running it over in m.p.e.worksheet.functions also. Something for you to consider for the future... From a post by Jeff Johnson: "You have posted this question individually to multiple groups. This is called Multiposting and it's BAD. Replies made in one group will not be visible in the other groups, which may cause multiple people to respond to your question with the same answer because they didn't know someone else had already done it. This is a waste of time. If you MUST post your message to multiple groups, post a single message and select all the groups (or type their names manually in the Newsgroups field, separated by commas) in which you want it to be seen. This is called Crossposting and when used properly it is GOOD." Some additional comment previously posted by me: "You may not see this as a problem, but those of us who volunteer answering questions on newsgroups do see it as a problem. You can't imagine how annoying it is for a volunteer to read a question, research background material, test sample code and then formulate and post an answer to the original question only to go to another newsgroup and find the question posted and ALREADY answered over there. On top of that, if you cross-post your question, all of the readers in all the newsgroups it is cross-posted to get to see both the original question and all of the answers given to it. This is beneficial to you because then we can add additional material to, add clarification to, as well as add additional examples to an answer you have received previously... that means you end up with a more complete solution to your problem. This is a win-win situation for all of us." Rick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct multiplying instead of adding | Excel Worksheet Functions | |||
Multiplying two rows together | Excel Worksheet Functions | |||
adding three consecutive columns | Excel Worksheet Functions | |||
Adding Consecutive Entries | Excel Discussion (Misc queries) | |||
Adding Consecutive Entries | Excel Discussion (Misc queries) |