Sumproduct Question

[jamtay317]

Hello It seems that I am having some trouble with sumproduct.

Im trying t convert this into sumproduct

=((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.

 

[J.Ty.]

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]

Im trying t convert this into sumproduct

=((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:

=((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:

=((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.