| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
multiply arrays
What's wrong with this formula?
=SUMPRODUCT(SUMIF(D110:D114,{"0"},G110:G114*'ROI Questions'!B34:B38)) |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
multiply arrays
In
=SUMPRODUCT(SUMIF(D110:D114,{"0"},G110:G114*'ROI Questions'!B34:B38)) replace "0"} with "0" Add ) after G114 and ( before 'ROI in other words try =SUMPRODUCT(SUMIF(D110:D114,"0",G110:G114)*('ROI Questions'!B34:B38)) What do you actually want to calculate? "Gary" wrote: What's wrong with this formula? =SUMPRODUCT(SUMIF(D110:D114,{"0"},G110:G114*'ROI Questions'!B34:B38)) |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
multiply arrays
I'm simply trying to sum the product of multiplying two arrays on the same
spreadsheet when both columns are <0. If I can do that, then I want to try to multiply and sum arrays on two different spreadsheets in the some workbook. "Sheeloo" wrote: In =SUMPRODUCT(SUMIF(D110:D114,{"0"},G110:G114*'ROI Questions'!B34:B38)) replace "0"} with "0" Add ) after G114 and ( before 'ROI in other words try =SUMPRODUCT(SUMIF(D110:D114,"0",G110:G114)*('ROI Questions'!B34:B38)) What do you actually want to calculate? "Gary" wrote: What's wrong with this formula? =SUMPRODUCT(SUMIF(D110:D114,{"0"},G110:G114*'ROI Questions'!B34:B38)) |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
multiply arrays
Try
=SUMPRODUCT(--(D110:D1140),G110:G114,--('ROI Questions'!B34:B38),('ROI Questions'!B34:B38)) This will multiply the corresoponding elements of Col D and ROI Col B and then SUM them, when both are greater than zero. In fact =SUMPRODUCT(--(D110:D1140),G110:G114,('ROI Questions'!B34:B38)) will also give you the same result, since multiplying zero values won't add up anything. "Gary" wrote: I'm simply trying to sum the product of multiplying two arrays on the same spreadsheet when both columns are <0. If I can do that, then I want to try to multiply and sum arrays on two different spreadsheets in the some workbook. "Sheeloo" wrote: In =SUMPRODUCT(SUMIF(D110:D114,{"0"},G110:G114*'ROI Questions'!B34:B38)) replace "0"} with "0" Add ) after G114 and ( before 'ROI in other words try =SUMPRODUCT(SUMIF(D110:D114,"0",G110:G114)*('ROI Questions'!B34:B38)) What do you actually want to calculate? "Gary" wrote: What's wrong with this formula? =SUMPRODUCT(SUMIF(D110:D114,{"0"},G110:G114*'ROI Questions'!B34:B38)) |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
multiply arrays
=SUMPRODUCT(SUMIF(D110:D114,{"0"},G110:G114*'R OI Questions'!B34:B38))
Maybe this: =SUMPRODUCT(--(D110:D1140),G110:G114,'ROI Questions'!B34:B38) -- Biff Microsoft Excel MVP "Gary" wrote in message ... What's wrong with this formula? =SUMPRODUCT(SUMIF(D110:D114,{"0"},G110:G114*'ROI Questions'!B34:B38)) |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
multiply arrays
=SUMPRODUCT(--(D110:D1140),G110:G114,--('ROI Questions'!B34:B38),('ROI
Questions'!B34:B38)) You've repeated the last array: ....--('ROI Questions'!B34:B38),('ROI Questions'!B34:B38) Maybe this is what they're after: =SUMPRODUCT(--(D110:D1140),G110:G114,'ROI Questions'!B34:B38) -- Biff Microsoft Excel MVP "Sheeloo" wrote in message ... Try =SUMPRODUCT(--(D110:D1140),G110:G114,--('ROI Questions'!B34:B38),('ROI Questions'!B34:B38)) This will multiply the corresoponding elements of Col D and ROI Col B and then SUM them, when both are greater than zero. In fact =SUMPRODUCT(--(D110:D1140),G110:G114,('ROI Questions'!B34:B38)) will also give you the same result, since multiplying zero values won't add up anything. "Gary" wrote: I'm simply trying to sum the product of multiplying two arrays on the same spreadsheet when both columns are <0. If I can do that, then I want to try to multiply and sum arrays on two different spreadsheets in the some workbook. "Sheeloo" wrote: In =SUMPRODUCT(SUMIF(D110:D114,{"0"},G110:G114*'ROI Questions'!B34:B38)) replace "0"} with "0" Add ) after G114 and ( before 'ROI in other words try =SUMPRODUCT(SUMIF(D110:D114,"0",G110:G114)*('ROI Questions'!B34:B38)) What do you actually want to calculate? "Gary" wrote: What's wrong with this formula? =SUMPRODUCT(SUMIF(D110:D114,{"0"},G110:G114*'ROI Questions'!B34:B38)) |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
multiply arrays
Thank you! This worked like a charm:
=SUMPRODUCT(--(D110:D1140),G110:G114,('ROI Questions'!B34:B38)) "Sheeloo" wrote: Try =SUMPRODUCT(--(D110:D1140),G110:G114,--('ROI Questions'!B34:B38),('ROI Questions'!B34:B38)) This will multiply the corresoponding elements of Col D and ROI Col B and then SUM them, when both are greater than zero. In fact =SUMPRODUCT(--(D110:D1140),G110:G114,('ROI Questions'!B34:B38)) will also give you the same result, since multiplying zero values won't add up anything. "Gary" wrote: I'm simply trying to sum the product of multiplying two arrays on the same spreadsheet when both columns are <0. If I can do that, then I want to try to multiply and sum arrays on two different spreadsheets in the some workbook. "Sheeloo" wrote: In =SUMPRODUCT(SUMIF(D110:D114,{"0"},G110:G114*'ROI Questions'!B34:B38)) replace "0"} with "0" Add ) after G114 and ( before 'ROI in other words try =SUMPRODUCT(SUMIF(D110:D114,"0",G110:G114)*('ROI Questions'!B34:B38)) What do you actually want to calculate? "Gary" wrote: What's wrong with this formula? =SUMPRODUCT(SUMIF(D110:D114,{"0"},G110:G114*'ROI Questions'!B34:B38)) |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
multiply arrays
Hello Biff,
That was intentional. I was trying to answer the way he had asked the question... I did point out that =SUMPRODUCT(--(D110:D1140),G110:G114,('ROI Questions'!B34:B38)) will also give you the same result, since multiplying zero values won't add up anything. Regards, Sheeloo "T. Valko" wrote: =SUMPRODUCT(--(D110:D1140),G110:G114,--('ROI Questions'!B34:B38),('ROI Questions'!B34:B38)) You've repeated the last array: ....--('ROI Questions'!B34:B38),('ROI Questions'!B34:B38) Maybe this is what they're after: =SUMPRODUCT(--(D110:D1140),G110:G114,'ROI Questions'!B34:B38) -- Biff Microsoft Excel MVP "Sheeloo" wrote in message ... Try =SUMPRODUCT(--(D110:D1140),G110:G114,--('ROI Questions'!B34:B38),('ROI Questions'!B34:B38)) This will multiply the corresoponding elements of Col D and ROI Col B and then SUM them, when both are greater than zero. In fact =SUMPRODUCT(--(D110:D1140),G110:G114,('ROI Questions'!B34:B38)) will also give you the same result, since multiplying zero values won't add up anything. "Gary" wrote: I'm simply trying to sum the product of multiplying two arrays on the same spreadsheet when both columns are <0. If I can do that, then I want to try to multiply and sum arrays on two different spreadsheets in the some workbook. "Sheeloo" wrote: In =SUMPRODUCT(SUMIF(D110:D114,{"0"},G110:G114*'ROI Questions'!B34:B38)) replace "0"} with "0" Add ) after G114 and ( before 'ROI in other words try =SUMPRODUCT(SUMIF(D110:D114,"0",G110:G114)*('ROI Questions'!B34:B38)) What do you actually want to calculate? "Gary" wrote: What's wrong with this formula? =SUMPRODUCT(SUMIF(D110:D114,{"0"},G110:G114*'ROI Questions'!B34:B38)) |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
multiply arrays
OK, next step.
These two formulas work separately and produce my desired result: =IF(G1100,SUMPRODUCT(--(D110:D1120),G110:G112,'ROI Questions'!B35:B37))+(D114/E114)*G114 =IF(G110<0,D110/E110*G110*-1+SUMPRODUCT(--(D111:D1120),G111:G112,'ROI Questions'!B36:B37))+(D114/E114)*G114 But when I combine the two IF statements as below, I get a VALUE error. Where is my error? =IF(G1100,SUMPRODUCT(--(D110:D1120),G110:G112,'ROI Questions'!B35:B37))+(D114/E114)*G114,IF(G110<0,D110/E110*G110*-1+SUMPRODUCT(--(D111:D1120),G111:G112,'ROI Questions'!B36:B37))+(D114/E114)*G114 Also, in the two independent formulas in the first part of this question above, before combining the two IF statements, I don't understand why the IF statements don't need a closing paranthesis to end each formula. This is a new one to me. "Sheeloo" wrote: Try =SUMPRODUCT(--(D110:D1140),G110:G114,--('ROI Questions'!B34:B38),('ROI Questions'!B34:B38)) This will multiply the corresoponding elements of Col D and ROI Col B and then SUM them, when both are greater than zero. In fact =SUMPRODUCT(--(D110:D1140),G110:G114,('ROI Questions'!B34:B38)) will also give you the same result, since multiplying zero values won't add up anything. "Gary" wrote: I'm simply trying to sum the product of multiplying two arrays on the same spreadsheet when both columns are <0. If I can do that, then I want to try to multiply and sum arrays on two different spreadsheets in the some workbook. "Sheeloo" wrote: In =SUMPRODUCT(SUMIF(D110:D114,{"0"},G110:G114*'ROI Questions'!B34:B38)) replace "0"} with "0" Add ) after G114 and ( before 'ROI in other words try =SUMPRODUCT(SUMIF(D110:D114,"0",G110:G114)*('ROI Questions'!B34:B38)) What do you actually want to calculate? "Gary" wrote: What's wrong with this formula? =SUMPRODUCT(SUMIF(D110:D114,{"0"},G110:G114*'ROI Questions'!B34:B38)) |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
multiply arrays
I found my mistake. Actually my question about the closing parenthesis after
the IF statements got me thinking that my parenthesis were in the wrong place, so the IF statements weren't evaluating the entire formula. This works: =IF(G1100,SUMPRODUCT(--(D110:D1120),G110:G112,'ROI Questions'!B35:B37)+(D114/E114)*G114,IF(G110<0,D110/E110*G110*-1+SUMPRODUCT(--(D111:D1120),G111:G112,'ROI Questions'!B36:B37)+(D114/E114)*G114,)) "Gary" wrote: OK, next step. These two formulas work separately and produce my desired result: =IF(G1100,SUMPRODUCT(--(D110:D1120),G110:G112,'ROI Questions'!B35:B37))+(D114/E114)*G114 =IF(G110<0,D110/E110*G110*-1+SUMPRODUCT(--(D111:D1120),G111:G112,'ROI Questions'!B36:B37))+(D114/E114)*G114 But when I combine the two IF statements as below, I get a VALUE error. Where is my error? =IF(G1100,SUMPRODUCT(--(D110:D1120),G110:G112,'ROI Questions'!B35:B37))+(D114/E114)*G114,IF(G110<0,D110/E110*G110*-1+SUMPRODUCT(--(D111:D1120),G111:G112,'ROI Questions'!B36:B37))+(D114/E114)*G114 Also, in the two independent formulas in the first part of this question above, before combining the two IF statements, I don't understand why the IF statements don't need a closing paranthesis to end each formula. This is a new one to me. "Sheeloo" wrote: Try =SUMPRODUCT(--(D110:D1140),G110:G114,--('ROI Questions'!B34:B38),('ROI Questions'!B34:B38)) This will multiply the corresoponding elements of Col D and ROI Col B and then SUM them, when both are greater than zero. In fact =SUMPRODUCT(--(D110:D1140),G110:G114,('ROI Questions'!B34:B38)) will also give you the same result, since multiplying zero values won't add up anything. "Gary" wrote: I'm simply trying to sum the product of multiplying two arrays on the same spreadsheet when both columns are <0. If I can do that, then I want to try to multiply and sum arrays on two different spreadsheets in the some workbook. "Sheeloo" wrote: In =SUMPRODUCT(SUMIF(D110:D114,{"0"},G110:G114*'ROI Questions'!B34:B38)) replace "0"} with "0" Add ) after G114 and ( before 'ROI in other words try =SUMPRODUCT(SUMIF(D110:D114,"0",G110:G114)*('ROI Questions'!B34:B38)) What do you actually want to calculate? "Gary" wrote: What's wrong with this formula? =SUMPRODUCT(SUMIF(D110:D114,{"0"},G110:G114*'ROI Questions'!B34:B38)) |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
multiply arrays
Glad you worked it out...
When you type ) it briefly show the matching ( .... this is a good way to find the pair Another option is to click on select on part of the formula and click fx or use Evaluate to see how the formula is evaluated Safest way is to enter () together and then put the elements in e.g. =IF(,,) and then put the apprpriate elements "Gary" wrote: I found my mistake. Actually my question about the closing parenthesis after the IF statements got me thinking that my parenthesis were in the wrong place, so the IF statements weren't evaluating the entire formula. This works: =IF(G1100,SUMPRODUCT(--(D110:D1120),G110:G112,'ROI Questions'!B35:B37)+(D114/E114)*G114,IF(G110<0,D110/E110*G110*-1+SUMPRODUCT(--(D111:D1120),G111:G112,'ROI Questions'!B36:B37)+(D114/E114)*G114,)) "Gary" wrote: OK, next step. These two formulas work separately and produce my desired result: =IF(G1100,SUMPRODUCT(--(D110:D1120),G110:G112,'ROI Questions'!B35:B37))+(D114/E114)*G114 =IF(G110<0,D110/E110*G110*-1+SUMPRODUCT(--(D111:D1120),G111:G112,'ROI Questions'!B36:B37))+(D114/E114)*G114 But when I combine the two IF statements as below, I get a VALUE error. Where is my error? =IF(G1100,SUMPRODUCT(--(D110:D1120),G110:G112,'ROI Questions'!B35:B37))+(D114/E114)*G114,IF(G110<0,D110/E110*G110*-1+SUMPRODUCT(--(D111:D1120),G111:G112,'ROI Questions'!B36:B37))+(D114/E114)*G114 Also, in the two independent formulas in the first part of this question above, before combining the two IF statements, I don't understand why the IF statements don't need a closing paranthesis to end each formula. This is a new one to me. "Sheeloo" wrote: Try =SUMPRODUCT(--(D110:D1140),G110:G114,--('ROI Questions'!B34:B38),('ROI Questions'!B34:B38)) This will multiply the corresoponding elements of Col D and ROI Col B and then SUM them, when both are greater than zero. In fact =SUMPRODUCT(--(D110:D1140),G110:G114,('ROI Questions'!B34:B38)) will also give you the same result, since multiplying zero values won't add up anything. "Gary" wrote: I'm simply trying to sum the product of multiplying two arrays on the same spreadsheet when both columns are <0. If I can do that, then I want to try to multiply and sum arrays on two different spreadsheets in the some workbook. "Sheeloo" wrote: In =SUMPRODUCT(SUMIF(D110:D114,{"0"},G110:G114*'ROI Questions'!B34:B38)) replace "0"} with "0" Add ) after G114 and ( before 'ROI in other words try =SUMPRODUCT(SUMIF(D110:D114,"0",G110:G114)*('ROI Questions'!B34:B38)) What do you actually want to calculate? "Gary" wrote: What's wrong with this formula? =SUMPRODUCT(SUMIF(D110:D114,{"0"},G110:G114*'ROI Questions'!B34:B38)) |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
multiply arrays
I just discovered I still have a problem.
This part of my formula G111:G112,'ROI Questions'!B36:B37 is not computing the multiplication of the two arrays. The formula adds the values in G111:G112, but does not multiply by 'ROI Questions'!B36:B37. "Sheeloo" wrote: Glad you worked it out... When you type ) it briefly show the matching ( .... this is a good way to find the pair Another option is to click on select on part of the formula and click fx or use Evaluate to see how the formula is evaluated Safest way is to enter () together and then put the elements in e.g. =IF(,,) and then put the apprpriate elements "Gary" wrote: I found my mistake. Actually my question about the closing parenthesis after the IF statements got me thinking that my parenthesis were in the wrong place, so the IF statements weren't evaluating the entire formula. This works: =IF(G1100,SUMPRODUCT(--(D110:D1120),G110:G112,'ROI Questions'!B35:B37)+(D114/E114)*G114,IF(G110<0,D110/E110*G110*-1+SUMPRODUCT(--(D111:D1120),G111:G112,'ROI Questions'!B36:B37)+(D114/E114)*G114,)) "Gary" wrote: OK, next step. These two formulas work separately and produce my desired result: =IF(G1100,SUMPRODUCT(--(D110:D1120),G110:G112,'ROI Questions'!B35:B37))+(D114/E114)*G114 =IF(G110<0,D110/E110*G110*-1+SUMPRODUCT(--(D111:D1120),G111:G112,'ROI Questions'!B36:B37))+(D114/E114)*G114 But when I combine the two IF statements as below, I get a VALUE error. Where is my error? =IF(G1100,SUMPRODUCT(--(D110:D1120),G110:G112,'ROI Questions'!B35:B37))+(D114/E114)*G114,IF(G110<0,D110/E110*G110*-1+SUMPRODUCT(--(D111:D1120),G111:G112,'ROI Questions'!B36:B37))+(D114/E114)*G114 Also, in the two independent formulas in the first part of this question above, before combining the two IF statements, I don't understand why the IF statements don't need a closing paranthesis to end each formula. This is a new one to me. "Sheeloo" wrote: Try =SUMPRODUCT(--(D110:D1140),G110:G114,--('ROI Questions'!B34:B38),('ROI Questions'!B34:B38)) This will multiply the corresoponding elements of Col D and ROI Col B and then SUM them, when both are greater than zero. In fact =SUMPRODUCT(--(D110:D1140),G110:G114,('ROI Questions'!B34:B38)) will also give you the same result, since multiplying zero values won't add up anything. "Gary" wrote: I'm simply trying to sum the product of multiplying two arrays on the same spreadsheet when both columns are <0. If I can do that, then I want to try to multiply and sum arrays on two different spreadsheets in the some workbook. "Sheeloo" wrote: In =SUMPRODUCT(SUMIF(D110:D114,{"0"},G110:G114*'ROI Questions'!B34:B38)) replace "0"} with "0" Add ) after G114 and ( before 'ROI in other words try =SUMPRODUCT(SUMIF(D110:D114,"0",G110:G114)*('ROI Questions'!B34:B38)) What do you actually want to calculate? "Gary" wrote: What's wrong with this formula? =SUMPRODUCT(SUMIF(D110:D114,{"0"},G110:G114*'ROI Questions'!B34:B38)) |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
multiply arrays
Pl. paste the formula so that we can see...
"Gary" wrote: I just discovered I still have a problem. This part of my formula G111:G112,'ROI Questions'!B36:B37 is not computing the multiplication of the two arrays. The formula adds the values in G111:G112, but does not multiply by 'ROI Questions'!B36:B37. |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
multiply arrays
I solved it!
In the second part of my formula IF(G110<0,(D110/E110*G110*-1)+SUMPRODUCT(--(D111:D1120),G111:G112,'ROI Questions'!B36:B37)+(D114/E114)*G114,) I moved D110/E110*G110*-1 and placed it after the SUMPRODUCT function as follows: IF(G110<0,SUMPRODUCT(--(D111:D1120),G111:G112,'ROI Questions'!B36:B37)+(D110/E110*G110*-1)+(D114/E114)*G114,) Must the SUMPRODUCT function always be first in a formula? "Gary" wrote: I just discovered I still have a problem. This part of my formula G111:G112,'ROI Questions'!B36:B37 is not computing the multiplication of the two arrays. The formula adds the values in G111:G112, but does not multiply by 'ROI Questions'!B36:B37. "Sheeloo" wrote: Glad you worked it out... When you type ) it briefly show the matching ( .... this is a good way to find the pair Another option is to click on select on part of the formula and click fx or use Evaluate to see how the formula is evaluated Safest way is to enter () together and then put the elements in e.g. =IF(,,) and then put the apprpriate elements "Gary" wrote: I found my mistake. Actually my question about the closing parenthesis after the IF statements got me thinking that my parenthesis were in the wrong place, so the IF statements weren't evaluating the entire formula. This works: =IF(G1100,SUMPRODUCT(--(D110:D1120),G110:G112,'ROI Questions'!B35:B37)+(D114/E114)*G114,IF(G110<0,D110/E110*G110*-1+SUMPRODUCT(--(D111:D1120),G111:G112,'ROI Questions'!B36:B37)+(D114/E114)*G114,)) "Gary" wrote: OK, next step. These two formulas work separately and produce my desired result: =IF(G1100,SUMPRODUCT(--(D110:D1120),G110:G112,'ROI Questions'!B35:B37))+(D114/E114)*G114 =IF(G110<0,D110/E110*G110*-1+SUMPRODUCT(--(D111:D1120),G111:G112,'ROI Questions'!B36:B37))+(D114/E114)*G114 But when I combine the two IF statements as below, I get a VALUE error. Where is my error? =IF(G1100,SUMPRODUCT(--(D110:D1120),G110:G112,'ROI Questions'!B35:B37))+(D114/E114)*G114,IF(G110<0,D110/E110*G110*-1+SUMPRODUCT(--(D111:D1120),G111:G112,'ROI Questions'!B36:B37))+(D114/E114)*G114 Also, in the two independent formulas in the first part of this question above, before combining the two IF statements, I don't understand why the IF statements don't need a closing paranthesis to end each formula. This is a new one to me. "Sheeloo" wrote: Try =SUMPRODUCT(--(D110:D1140),G110:G114,--('ROI Questions'!B34:B38),('ROI Questions'!B34:B38)) This will multiply the corresoponding elements of Col D and ROI Col B and then SUM them, when both are greater than zero. In fact =SUMPRODUCT(--(D110:D1140),G110:G114,('ROI Questions'!B34:B38)) will also give you the same result, since multiplying zero values won't add up anything. "Gary" wrote: I'm simply trying to sum the product of multiplying two arrays on the same spreadsheet when both columns are <0. If I can do that, then I want to try to multiply and sum arrays on two different spreadsheets in the some workbook. "Sheeloo" wrote: In =SUMPRODUCT(SUMIF(D110:D114,{"0"},G110:G114*'ROI Questions'!B34:B38)) replace "0"} with "0" Add ) after G114 and ( before 'ROI in other words try =SUMPRODUCT(SUMIF(D110:D114,"0",G110:G114)*('ROI Questions'!B34:B38)) What do you actually want to calculate? "Gary" wrote: What's wrong with this formula? =SUMPRODUCT(SUMIF(D110:D114,{"0"},G110:G114*'ROI Questions'!B34:B38)) |
|
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
multiply arrays
Order is not important...
btw I am getting the same answer with both, as I should. "Gary" wrote: I solved it! In the second part of my formula IF(G110<0,(D110/E110*G110*-1)+SUMPRODUCT(--(D111:D1120),G111:G112,'ROI Questions'!B36:B37)+(D114/E114)*G114,) I moved D110/E110*G110*-1 and placed it after the SUMPRODUCT function as follows: IF(G110<0,SUMPRODUCT(--(D111:D1120),G111:G112,'ROI Questions'!B36:B37)+(D110/E110*G110*-1)+(D114/E114)*G114,) Must the SUMPRODUCT function always be first in a formula? "Gary" wrote: I just discovered I still have a problem. This part of my formula G111:G112,'ROI Questions'!B36:B37 is not computing the multiplication of the two arrays. The formula adds the values in G111:G112, but does not multiply by 'ROI Questions'!B36:B37. "Sheeloo" wrote: Glad you worked it out... When you type ) it briefly show the matching ( .... this is a good way to find the pair Another option is to click on select on part of the formula and click fx or use Evaluate to see how the formula is evaluated Safest way is to enter () together and then put the elements in e.g. =IF(,,) and then put the apprpriate elements "Gary" wrote: I found my mistake. Actually my question about the closing parenthesis after the IF statements got me thinking that my parenthesis were in the wrong place, so the IF statements weren't evaluating the entire formula. This works: =IF(G1100,SUMPRODUCT(--(D110:D1120),G110:G112,'ROI Questions'!B35:B37)+(D114/E114)*G114,IF(G110<0,D110/E110*G110*-1+SUMPRODUCT(--(D111:D1120),G111:G112,'ROI Questions'!B36:B37)+(D114/E114)*G114,)) "Gary" wrote: OK, next step. These two formulas work separately and produce my desired result: =IF(G1100,SUMPRODUCT(--(D110:D1120),G110:G112,'ROI Questions'!B35:B37))+(D114/E114)*G114 =IF(G110<0,D110/E110*G110*-1+SUMPRODUCT(--(D111:D1120),G111:G112,'ROI Questions'!B36:B37))+(D114/E114)*G114 But when I combine the two IF statements as below, I get a VALUE error. Where is my error? =IF(G1100,SUMPRODUCT(--(D110:D1120),G110:G112,'ROI Questions'!B35:B37))+(D114/E114)*G114,IF(G110<0,D110/E110*G110*-1+SUMPRODUCT(--(D111:D1120),G111:G112,'ROI Questions'!B36:B37))+(D114/E114)*G114 Also, in the two independent formulas in the first part of this question above, before combining the two IF statements, I don't understand why the IF statements don't need a closing paranthesis to end each formula. This is a new one to me. "Sheeloo" wrote: Try =SUMPRODUCT(--(D110:D1140),G110:G114,--('ROI Questions'!B34:B38),('ROI Questions'!B34:B38)) This will multiply the corresoponding elements of Col D and ROI Col B and then SUM them, when both are greater than zero. In fact =SUMPRODUCT(--(D110:D1140),G110:G114,('ROI Questions'!B34:B38)) will also give you the same result, since multiplying zero values won't add up anything. "Gary" wrote: I'm simply trying to sum the product of multiplying two arrays on the same spreadsheet when both columns are <0. If I can do that, then I want to try to multiply and sum arrays on two different spreadsheets in the some workbook. "Sheeloo" wrote: In =SUMPRODUCT(SUMIF(D110:D114,{"0"},G110:G114*'ROI Questions'!B34:B38)) replace "0"} with "0" Add ) after G114 and ( before 'ROI in other words try =SUMPRODUCT(SUMIF(D110:D114,"0",G110:G114)*('ROI Questions'!B34:B38)) What do you actually want to calculate? "Gary" wrote: What's wrong with this formula? =SUMPRODUCT(SUMIF(D110:D114,{"0"},G110:G114*'ROI Questions'!B34:B38)) |
|
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
multiply arrays
Download http://wikisend.com/download/523972/Sample SUMPRODUCT.xls and see...
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Avg Arrays | Excel Worksheet Functions | |||
| add value of 4 cells, multiply by 3 subtract 72 multiply by 80% | Excel Discussion (Misc queries) | |||
| 3D Arrays | Excel Worksheet Functions | |||
| Arrays | Excel Worksheet Functions | |||
| Multiply non-contigious arrays | Excel Discussion (Misc queries) | |||
Linear Mode
