Hi,
I have monthly sales data for two products arranged as follows:
Column A: Consecutive month-end dates (e.g. 31/12/2018, 31/01/2019, 28/02/2019 etc)
Column B: Monthly sales values for Product A
Column C: Monthly sales values for Product B
I would like to create a formula that will calculate the % of months in a stipulated period for which the sales value of Product A was greater than the sales of Product B.
For example, in 5 years (60 months), the sales of Product A was greater than that of Product B in 36 months, so the % returned by the formula is 60% (i.e. 36/60).
I would also like this formula to take into account a start and end date in cells D1 and E1, which will define the period for which the % is to be calculated (e.g. if D1 is 31/12/2017 and E1 is 31/12/2018, the % will be calculated over that 12 month period).
Can someone please suggest a formula that will accomplish all of this?
Thanks!
I have monthly sales data for two products arranged as follows:
Column A: Consecutive month-end dates (e.g. 31/12/2018, 31/01/2019, 28/02/2019 etc)
Column B: Monthly sales values for Product A
Column C: Monthly sales values for Product B
I would like to create a formula that will calculate the % of months in a stipulated period for which the sales value of Product A was greater than the sales of Product B.
For example, in 5 years (60 months), the sales of Product A was greater than that of Product B in 36 months, so the % returned by the formula is 60% (i.e. 36/60).
I would also like this formula to take into account a start and end date in cells D1 and E1, which will define the period for which the % is to be calculated (e.g. if D1 is 31/12/2017 and E1 is 31/12/2018, the % will be calculated over that 12 month period).
Can someone please suggest a formula that will accomplish all of this?
Thanks!