2

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)
1
  • 3
    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. Nov 18, 2016 at 14:31

2 Answers 2

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

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.

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .