Replace indirect formula

Hi,

In E12:

=(PRODUCT(INDEX(E:E,$A12):INDEX(E:E,$B12))-1)*SIGN($D$12-$D$11)

This does not require CTRL+SHIFT+ENTER.

Same for rows 13-15 though remove the part with SIGN.

Regards

They could be removed and hard coded but since they depend on the row numbers defined in A12:A15 which aren't consistent they couldn;t be copied down. The Indirect would need to be replaced with an OFFSET function.

Is that what you really want?

It would be useful if you could explain why the span of rows defined for each yaer don't seem to be consistent, and in fact what the aim of thne calculation is.

Originally Posted by Richard Buttrey

The Indirect would need to be replaced with an OFFSET function.

Hi Richard,

Actually it's a surprisingly common misconception that the only two functions available in such scenarios are the volatile OFFSET and INDIRECT. The equivalent (and oft-neglected) INDEX set-up is preferable, being 'barely' volatile ('at workbook open' only: http://www.decisionmodels.com/calcsecretsi.htm).

Regards

You're welcome!

Cheers