ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   multiply arrays (https://www.excelbanter.com/excel-discussion-misc-queries/228267-multiply-arrays.html)

Gary
multiply arrays
 
What's wrong with this formula?

=SUMPRODUCT(SUMIF(D110:D114,{"0"},G110:G114*'ROI Questions'!B34:B38))

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

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

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

T. Valko
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))



T. Valko
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))



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

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




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

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

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

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

Sheeloo
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.

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

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

Sheeloo
multiply arrays
 
Download http://wikisend.com/download/523972/Sample SUMPRODUCT.xls and see...


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

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com