excelexcel-formulaexcel-indirect

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

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

Solution

  • 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