Using Indirect formula in Excel to get the product of 1 + Cell Range

1
votes

I'm trying to use Indirect with an array formula to get the product of 1 + a cell range (e.g. D5:D7). When I try to use a statement like the one below, I get a #VALUE! error.

={PRODUCT(1+INDIRECT("$D"&K5&":$D"&ROW($A4)))}

In the code above, K5 will contain a number less than Row($A4) (i.e. a number less than 4).

Can someone please suggest a way of accomplishing this?

Thanks very much

3
excelexcel-formulaexcel-indirect
The reason this doesn't work, specifically, is because of ROWfunction - ROW returns an array like {4} rather than just a number, even when it's a single value like here - some functions work OK with that but other functions can't handle it - to fix you can wrap ROW function in MAX or SUM which won't change the value but will turn it into a single value - =PRODUCT(1+INDIRECT("$D"&K5&":$D"&SUM(ROW($A4)))) – barry houdini

3 Answers

1
votes

This is arguably easier with INDEX function, i.e.

=PRODUCT(1+INDEX(D:D,K5):INDEX(D:D,ROW($A4)))

confirm with CTRL+SHIFT+ENTER

0
votes

Arguably the easiest way is to simply do:

PRODUCT((A2:A100)+1)

If you want to multiply the (1+cell value) of cells A1 to A100

Sure, it will generate a #VALUE! in the front end but if you then click on the insert formula (the little Fx next to the formula bar) you will see the formula result there.

This value is correct and accurate. I use it all the time for chain-linking returns and can’t be bothered with more complicated formulas.

0
votes

@Gabs Garcia, your approach is correct, however, I think that instead of everytime checking the value in the Insert Function Menu, it's easier just to use an Array Formula, which works as:

Enter:

=PRODUCT((A2:A100)+1)

Then instead of pressing ENTER, press CTRL+SHIFT+ENTER