Rolling Time Periods

[mttlltt75]

I need to find a way to calculate rolling time periods. The time periods I have are a rolling Week and rolling Month.
I need to count the weekends, but exclude their values within the formula.
So for the latest rolling week(last 5 business days) the dates I would be using are 2/21, 2/22, 2/25, 2/26, 2/27
Is there a formula I can use that automatically updates the cells in yellow when a new date and number is entered into the data range
The formula that I need to use is the =PRODUCT.
I have attached a workshet with the data I am working with. Thank you in advance for any help.

[rylo]

Hi

Exactly what formula are you using to get the results in the yellow highlighted cells? Doesn't seem to be like =PRODUCT(B37:B38,B41:B43)


rylo

[mttlltt75]

Hi Rylo,
I am using =product(B37:B38,B41:B43)-1

[daddylonglegs]

Try this formula in G3

=PRODUCT(IF($A1:$A100>=LARGE($A1:$A100,7),IF(B1:B100>0,B1:B100)))-1

This is an array formula which must be confirmed with CTRL+SHIFT+ENTER so that curly braces appear around the formula in the formula bar.

Format G3 as % and copy across to I3.

I presume that G4:I4 can be the same but with 7 changed to 30

[mttlltt75]

thanks daddylonglegs. that worked perfectly