Product type function H.e.l.p
 

Hi,
I would like to know if there is a function or combo of functions that can
produce the following long form calculation:

=(1+A2)*(1+A3)*(1+A4)-1
Where A2, A3 & A4 are percentages.

This needs to be done over a whole range of data so the above long form gets
a bit tricky.
I have tried the following formula "=PRODUCT(1+A2:A4)-1", but this does not
give me the same answer as the correct long form.

Thanks
George

Product type function H.e.l.p
 

Try entering your your PRODUCT formula as an array formula, i.e., committing
it with Ctrl-Shift-Enter


"George" wrote:

Hi,
I would like to know if there is a function or combo of functions that can
produce the following long form calculation:

=(1+A2)*(1+A3)*(1+A4)-1
Where A2, A3 & A4 are percentages.

This needs to be done over a whole range of data so the above long form gets
a bit tricky.
I have tried the following formula "=PRODUCT(1+A2:A4)-1", but this does not
give me the same answer as the correct long form.

Thanks
George

Product type function H.e.l.p
 

Thanks Duke,
this was extremely helpful.
Is it possible to do the array over a whole column without having to "F2"
each cell and then do the ctrl shift enter?
Much appreciated.
George

"Duke Carey" wrote:

Try entering your your PRODUCT formula as an array formula, i.e., committing
it with Ctrl-Shift-Enter


"George" wrote:

Hi,
I would like to know if there is a function or combo of functions that can
produce the following long form calculation:

=(1+A2)*(1+A3)*(1+A4)-1
Where A2, A3 & A4 are percentages.

This needs to be done over a whole range of data so the above long form gets
a bit tricky.
I have tried the following formula "=PRODUCT(1+A2:A4)-1", but this does not
give me the same answer as the correct long form.

Thanks
George