# Sumproduct Question

#### jamtay317

##### Well-known Member
Hello It seems that I am having some trouble with sumproduct.

Im trying t convert this into sumproduct
Code:
``=((R33*0.147)+((P31/1000000)*0.6)+((P32/1000000)*0.6)+(P33*0.0785)+(P34*0.071)+(P35*0.075)+((P36+Q36)*0.00002)+(P37*0.00002)+(P38*0.04)+(P39*0.0792)+(P40*0.04)+(P42*0.0014)+(P43*0.5413)+(P44*0.74))/2000)``

I would like to put the percentages in Q31:Q43.

All help is Appreciated.

### Excel Facts

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Well,

1. Your formula contains an error - an unbalanced parenthesis.
2. The cells it refers to are oddly and irregularly placed.

It seems to me that converting it to SUMPRODUCT will be difficult and counterproductive.

J.Ty.

Im trying t convert this into sumproduct
Code:
``````=((R33*0.147)+((P31/1000000)*0.6)+((P32/1000000)*0.6)+(P33*0.0785)
+(P34*0.071)+(P35*0.075)+((P36+Q36)*0.00002)+(P37*0.00002)+(P38*0.04)
+(P39*0.0792)+(P40*0.04)+(P42*0.0014)+(P43*0.5413)+(P44*0.74))/2000)``````

For what it's worth, you could write:
Code:
``````=((R33*0.147)+(Q36*0.00002)
+SUMPRODUCT(P31:P40,{0.0000006;0.0000006;0.0785;0.071;0.075;0.00002;0.00002;0.04;0.0792;0.04})
+SUMPRODUCT(P42:P44,{0.0014;0.5413;0.74}))/2000``````

Note the use of semicolon (";") instead of comma (",") in the array constant.

But see the caveat below regarding the use of constant decimal fractions.

I would like to put the percentages in Q31:Q43

I don't understand. First, you already have a value in Q36. Second, you have 15 decimal fractions that might be construed as percentages, including 0.147 and 0.00002.

If you mean just the 13 constants I put into array constants above, which I put into S31:S43 (unused), you might want:
Code:
``=((R33*0.147)+(Q36*0.00002)+SUMPRODUCT(P31:P40,S31:S40)+SUMPRODUCT(P42:P44,S41:S43))/2000``

Caveat: I suspect the constant decimal fractions ("percentages") are rounded representations of calculated values. If so, it would be better to put the formulas that calculate them into S31:S43, not the rounded representations as constants. The same applies to 0.147 and 0.00002 as well.

Replies
1
Views
412
Replies
3
Views
226
Replies
9
Views
417
Replies
4
Views
404
Replies
3
Views
391

1,219,696
Messages
6,149,778
Members
450,913
Latest member
mdsuther

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back