1

Let's say I have the following values in cells:

    A     B     C     D
1  0.1 | 0.5 | 0.3 | 2.1`

I am looking for the formula with which I can calculate without intermediate cells

(1 + 0.1) * (1 + 0.5) * (1 + 0.3) * (1 + 2.1)

What I've tried was (without success):

PRODUCT(1 + A1:D1)
  • 2
    Your formula will work if it is entered as an array formula. Confirm the formula with Ctrl-Shift-Enter instead of Enter when leaving edit mode. If done correctly then excel will put {} around the formula. – Scott Craner Nov 18 '16 at 14:31
2

(tried just commenting, but don't have enough rep in this community)

@Scott is correct, and make sure to spell the function name correctly - your post has a typo, missing U in PRODUCT(...).

| improve this answer | |
0

First of all thank you for the comments @Scott / @Marcin

The correct answer would then be:

In Google Sheets:

=PRODUCT(ARRAYFORMULA(A1:D1 + 1))

In Excel you have to finish your command with Ctrl + Shift + Enter or Cmd + Shift + Enter to get the correct result. The resulting field value will be

{=PRODUCT(A1:D1 + 1)}

Note: The {} cannot be entered manually.

| improve this answer | |
  • could you explain the downvote? This actually has been the full answer that I would have been looking for. – Matyas Nov 23 '16 at 9:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.