# 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

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

#### J.Ty.

##### Well-known Member
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.

#### joeu2004

##### Banned user
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
989
Replies
3
Views
175
Replies
0
Views
389
Replies
6
Views
1K
Replies
2
Views
225

### Forum statistics

1,191,086
Messages
5,984,531
Members
439,895
Latest member
ddkexcel ### 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