Conditional Array Formula

[macb]

I am trying create an Array formula using Product Function.

I have a table col1 with date and col2 with numbers for each date.

My Array formula is : =(Product(A2:A100/100+1)-1)*100).. This will
give me the product of that specified range.

I want to put an if condition within this formula in order to find the
Product between two periods.
I tried the following Array Formula, but it does not works:

=IF((F40>=A2:A100)*(F41<=A2:A100),(PRODUCT(A2:A100/100+1)-1)*100,1)

where F40 is start Date and F41 is end date.


Any help highly appreicated.

Thanks


mac

 

[smartin]

I am trying create an Array formula using Product Function.

I have a table col1 with date and col2 with numbers for each date.

My Array formula is : =(Product(A2:A100/100+1)-1)*100).. This will
give me the product of that specified range.

I want to put an if condition within this formula in order to find the
Product between two periods.
I tried the following Array Formula, but it does not works:

=IF((F40>=A2:A100)*(F41<=A2:A100),(PRODUCT(A2:A100/100+1)-1)*100,1)

where F40 is start Date and F41 is end date.


Any help highly appreicated.

Thanks


mac

Try this (array formula):

=PRODUCT(IF((F40>=A2:A100)*(F41<=A2:A100),((A2:A100/100+1)-1)*100,1))

 

[macb]

Try this (array formula):

=PRODUCT(IF((F40>=A2:A100)*(F41<=A2:A100),((A2:A100/100+1)-1)*100,1))- Hide quoted text -

- Show quoted text -

Thanks for the try. Does not worked. It gives me 0 result.


Regards

Mac

 

[macb]

Try this (array formula):

=PRODUCT(IF((F40>=A2:A100)*(F41<=A2:A100),((A2:A100/100+1)-1)*100,1))- Hide quoted text -

- Show quoted text -

Thanks. With some tweaks it worked.

Regards

Mac