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)
Matyas
Posted 2016-11-18T14:25:26.387
Reputation: 159
-1
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.
Matyas
Posted 2016-11-18T14:25:26.387
Reputation: 159
could you explain the downvote? This actually has been the full answer that I would have been looking for. – Matyas – 2016-11-23T09:16:55.483
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(...).
Marcin K
Posted 2016-11-18T14:25:26.387
Reputation: 46
2Your 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 – 2016-11-18T14:31:43.457