Array help

[bomon]

https://ibb.co/c4K7EQ

Hi,

I am pulling my hair out over this one.

Trying to calculate cumulative growth for all years within a specific period (before an exit date). You will see in P49 that the exit date is 2026 so I need to create an array that essentially does =(1+(E84)*(1+F84)*(1+G84)*(1+H84)*(1+I84)*(1+J84)*(1+K84)*(1+L84)*(1+M84)*(1+N84)*(1+O84)*(1+P84)*(1+Q84)*(1+R84)*(1+S84)*(1+T84)*(1+U84)*(1+V84)*(1+W84)*(1+X84)*(1+Y84)-1 but tests inside each as to whether each of these periods is equal to or less than YEAR(P49).

Please can some one help!

Thanks.

Bomon

 

[FormR]

Hi, you could try this - note: it's not clear from the image what row the "periods" are in - so for this example I've used row 83 (you will need to update as appropriate)

This is an array formula and requires entry with crtl+shift+enter.

=PRODUCT(IF(YEAR(E83:Y83)<YEAR(P49),E84:Y84+1))

 

[FormR]

=PRODUCT(IF(YEAR(E83:Y83)

<year(p49),e84:y84+1))[ quote]
Ooops- my suggestion got chopped.

=PRODUCT(IF(YEAR(E83:Y83)<=YEAR(P49),E84:Y84+1))</year(p49),e84:y84+1))[>

 

[istiasztalos]

Hi,

This forum often cuts off parts of posts when the < sign is used, (I assume because it is thinking it starts an HTML tag, but who knows.... )

Anyhow, I think FormR wanted to paste something like this

=PRODUCT(IF(YEAR(periods) < =YEAR(exit),1+grow,1))

Enter with CTRL+SHIFT+ENTER ! (Excel will add {} around it if done right)
'periods' is a named range containing your line of period headers, the formula assumes they are all valid dates and not just year numbers !
'exit' is a named range of the cell that has your exit date
'grow' is a named range containing your line of growth rates

edit**and i was too slow and you got the answer before I refreshed this page

 

[istiasztalos]

Excel continues to amaze me.... I thought I needed the 1 in case the IF's result is FALSE, because a FALSE is usually interpreted as 0 in calculations, hence the PRODUCT with any FALSE in there would result in 0.... but I tried yours and it is just ignored in the multiplication... lol