Array formula to compare values between two dates

[Graemea]

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!

 

[The_Macrotect]

Hi Graemea,

If that can be done with a formula, I don't know how. It certainly can be done with VBA though. I set up a sheet as you described:

Excel 2007 32 bit

	A	B	C	D	E	F	G	H
1	WE Date	Product A	Product B	30/9/2019	31/10/2020
2	31/12/2018	60667	61152
3	31/1/2019	43996	52628
4	28/2/2019	6563	36180
5	31/3/2019	67431	65401
6	30/4/2019	62530	31532
7	31/5/2019	25130	58111
8	30/6/2019	21161	20392
9	31/7/2019	72953	29917
10	31/8/2019	45347	40353
11	30/9/2019	47088	19899
12	31/10/2019	12007	31616
13	30/11/2019	20347	63457
14	31/12/2019	64445	30394
15	31/1/2020	51343	42754
16	29/2/2020	17912	40385
17	31/3/2020	42382	56656
18	30/4/2020	11876	48569
19	31/5/2020	37084	68953
20	30/6/2020	19998	71211
21	31/7/2020	64036	50609
22	31/8/2020	69359	45469
23	30/9/2020	49787	34811
24	31/10/2020	10979	24733
25	30/11/2020	48152	70693

<tbody>
</tbody>

Sheet1​

This VBA code will put your result in cell G1:
(This code assumes that values entered in your "start date" and "end date" cells will be month ending dates)

Sub calculate()
Dim startdate As Date, enddate As Date, x As Integer, y As Integer, z As Integer, count As Integer, lrow As Long

startdate = Range("D1").Value
enddate = Range("E1").Value
x = 2
count = 0
lrow = Cells(Rows.count, 1).End(xlUp).Row

Do While Cells(x, 1).Value <> startdate
If x > lrow Then
GoTo abort
Else
x = x + 1
End If
Loop

y = x + 1

Do While Cells(y, 1).Value <> enddate
If y > lrow Then
GoTo abort
Else
y = y + 1
End If
Loop

For z = x To y
If Cells(z, 2) > Cells(z, 3) Then
count = count + 1
Else
End If
Next z

range("G1").Value = count / ((y - x) + 1)
GoTo endofcode

abort:
MsgBox ("Error: Please check your date range and try again")

endofcode:
End Sub


All the best,
Matt

 

[AhoyNC]

Using example from The_Macrotect above.
See if this formula does what you want. I included the start and end dates in the formula. If you don't want them include just remove the = sign from the formula.

Excel Workbook
	A	B	C	D	E
1	WE Date	Product A	Product B	9/30/2019	10/31/2020
2	12/31/2018	60667	61152
3	1/31/2019	43996	52628		42.86%
4	2/28/2019	6563	36180
5	3/31/2019	67431	65401
6	4/30/2019	62530	31532
7	5/31/2019	25130	58111
8	6/30/2019	21161	20392
9	7/31/2019	72953	29917
10	8/31/2019	45347	40353
11	43738	47088	19899
12	43769	12007	31616
13	43799	20347	63457
14	43830	64445	30394
15	1/31/2020	51343	42754
16	2/29/2020	17912	40385
17	3/31/2020	42382	56656
18	4/30/2020	11876	48569
19	5/31/2020	37084	68953
20	6/30/2020	19998	71211
21	7/31/2020	64036	50609
22	8/31/2020	69359	45469
23	9/30/2020	49787	34811
24	10/31/2020	10979	24733
25	11/30/2020	48152	70693
Sheet