Multiply previous columns

[dorinb]

Hi,

For a set of percentages split by location and period , I need to obtain a detail for each location and for each period as a percentage of the previous months multiplied, as follows:

Data				Expected result
Loc	Posting Date	Percentage		Loc	Posting Date	Posting date cumulative	Percentage	Formula
Loc 1	01-Jan-18	10%		Loc 1	01-Jan-18	01-Jan-18	10.00%
Loc 1	01-Feb-18	5%		Loc 1	01-Feb-18	01-Jan-18	0.50%	%Jan * %Feb
Loc 1	01-Mar-18	17%		Loc 1	01-Feb-18	01-Feb-18	5.00%	% Feb
Loc 1	01-Apr-18	83%		Loc 1	01-Mar-18	01-Jan-18	0.09%	%Jan * %Feb*% Mar
Loc 1	01-May-18	25%		Loc 1	01-Mar-18	01-Feb-18	0.85%	%Feb*% Mar
Loc 1	01-Jun-18	99%		Loc 1	01-Mar-18	01-Mar-18	17.00%	%Mar
Loc 1	01-Jul-18	83%
Loc 1	01-Aug-18	73%
Loc 1	01-Sep-18	75%
Loc 1	01-Oct-18	76%
Loc 1	01-Nov-18	11%
Loc 1	01-Dec-18	98%
Loc 2	01-Jan-18	68%
Loc 2	01-Feb-18	49%
Loc 2	01-Mar-18	57%
Loc 2	01-Apr-18	60%
Loc 2	01-May-18	52%
Loc 2	01-Jun-18	78%

<tbody>
</tbody>

Any suggestions?
Cheers,
Dorin

 

[pgc01]

Hi Dorin

Assuming column D empty, like in the table you posted, in H2:

=PRODUCT(IF($A$2:$A$19=E2,IF($B$2:$B$19<=F2,IF($B$2:$B$19>=G2,$C$2:$C$19,1))))

This is an array formula, you have to confirm it with CTRL-SHIFT-ENTER.
Copy down

 

[pgc01]

Sorry, didn't see it was the BI forum. Disregard my answer.