Excel - Special product formula?

Question

Maybe someone can help me with the following problem in basic Excel because Google is unable to. I have a column of numbers, for example A1:A10 and I need the following operation:

I need the multiplication of all numbers (like =Product(A1:A10)) but I also need that each element before multiplication gets added by a constant 'c', for example 5.

So the column needs to be added with c and then all entries multiplicated.

I know you can just add the constant in the original column, but I need to do this for multiple values, so I don't want to copy my data constantly. Is there a formula which can do this? Something like =Product(A1:A10...+5).

score 1 · Accepted Answer · answered Oct 12 '13 at 14:46

1

You can use the formula you suggested

=PRODUCT(A1:A10+5)

....but that's an "array formula" so you have to enter with CTRL+SHIFT+ENTER key combination

Put formula in cell then select that cell and press F2 to select formula. Now hold down CTRL and SHIFT while presssing ENTER. If done correctly you will see curly braces around the formula so it looks like this:

{=PRODUCT(A1:A10+5)}

to avoid "array entry" you can add an INDEX function, i.e. this regular version

=PRODUCT(INDEX(A1:A10+5,0))

answered Oct 12 '13 at 14:46

barry houdini

45,615
8
63
81

Thanks, the index function didn't work, but the array formula did. – user2874101 Oct 12 '13 at 19:16
No problem - both formulas should give you the same result, though..... – barry houdini Oct 12 '13 at 20:05
The second gave me an error that it hadn't enough arguments I think. – user2874101 Oct 12 '13 at 20:19

score 0 · Answer 2 · answered Oct 12 '13 at 14:03

0

You can put the constant in a cell and refer to that cell in all your copied formulas using $ notation.

Another option is to actually put the constant as a column in the list of numbers and use the value of each row to multiply. BUT when putting in the constant, type in first line only and make all the others be the value of the line above. That way when you change the constant just in the first aline it changes all the way down the column.

answered Oct 12 '13 at 14:03

asantaballa

3,919
1
21
22

Could you give an example of the first method? I put 5 in cell B1 for example and use column A1:A10. How do I make the formula now? = Product(A1:A10...$??).. Thanks – user2874101 Oct 12 '13 at 14:21
Sorry for confusion. Was not thinking of using the PRODUCT formula directly and not sure if you can. Say you put the 5 in B1. Then you create column C with the formula "=(An + $B$1)" (n is row number for A1 - A10). This gives you a new column with the constant summed. Then you can use the PRODUCT formula to get the product of the new column. You do have to copy the formula, but once copied, you can change the constant in one spot. – asantaballa Oct 12 '13 at 18:37

Excel - Special product formula?

2 Answers2