# Array formula with array constant

#### Fdjaynes

##### New Member
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

 100% 88% 68% 50% 20% Calculated Total 1 10 20 29 15 6 2 30 30 0 0 20 3 150 0 0 0 10 4 500 0 100 120

<tbody>
</tbody>

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

#### RoryA

##### MrExcel MVP, Moderator
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

##### MrExcel MVP
Or this:

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

#### RoryA

##### MrExcel MVP, Moderator

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

##### New Member
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

##### MrExcel MVP, Moderator

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

##### MrExcel MVP
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

##### MrExcel MVP
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.

Last edited:

#### Fdjaynes

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

Replies
4
Views
199
Replies
5
Views
252
Replies
5
Views
243
Replies
2
Views
83
Replies
9
Views
122

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,152,193
Messages
5,768,772
Members
425,492
Latest member
blueexcel123

### 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