Array formula with array constant

[Fdjaynes]

I need help with creating an array formula for a spreadsheet I'm developing. The percentages are constants. In the calculated total column I want the sum of 100%*10 + 88%*20 + 68%*29 + 50%*15 + 20%*6 for each line. Any help or guidance will be greatly appreciated. - Fred

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]100%[/TD]
[TD]88%[/TD]
[TD]68%[/TD]
[TD]50%[/TD]
[TD]20%[/TD]
[TD]Calculated Total[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]10[/TD]
[TD]20[/TD]
[TD]29[/TD]
[TD]15[/TD]
[TD]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]30[/TD]
[TD]30[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]150[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]500[/TD]
[TD]0[/TD]
[TD]100[/TD]
[TD]120[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[RoryA]

Welcome to the forum.

If you enter those percentages in cells A1:E1, and your data then starts in row 2, enter this into F2:
=SUMPRODUCT($A$1:$E$1,A2:E2)
and then copy down as far as needed.

 

[Tetra201]

Or this:

=SUMPRODUCT($A$1:$E$1,{1,0.88,0.68,0.5,0.2})

 

[Fdjaynes]

Thank you for your help.

 

[RoryA]

Or this:

=SUMPRODUCT($A$1:$E$1,{1,0.88,0.68,0.5,0.2})

Yes, but I can't see any benefit to hardcoding the percentages. I'd always recommend putting them into cells for ease of maintenance so you only have to change them in one place.

 

[Fdjaynes]

Now I need to add another calculation with another constant. So that the calculated total for line one would be :

100%*10*$500 + 88%*20*$500 + 68%*29*$500 + 50%*15*$500 + 20%*6*$500

 

[RoryA]

Insert another row (say row 2) with those constant values, and use this:

=SUMPRODUCT($A$1:$E$1,$A$2:$E$2,A3:E3)

 

[Tetra201]

Yes, but I can't see any benefit to hardcoding the percentages. I'd always recommend putting them into cells for ease of maintenance so you only have to change them in one place.

Just as an option. Also, the array constant can be assigned to a name.

On benefits of hardcoding -- the values in cells can be inadvertently changed.

Finally, my formula has to be corrected to =SUMPRODUCT($A1:$E1,{1,0.88,0.68,0.5,0.2})

 

[Marcelo Branco]

Yes, but I can't see any benefit to hardcoding the percentages. I'd always recommend putting them into cells for ease of maintenance so you only have to change them in one place.

@Tetra

In addition to what Rory said, i think you meant
=SUMPRODUCT($A2:$E2,{1,0.88,0.68,0.5,0.2})

M.

 

[Fdjaynes]

Instead of inserting a row can you reference a cell for the constant?