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

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]WE Date[/TD]
[TD="align: center"]Product A[/TD]
[TD="align: center"]Product B[/TD]
[TD="align: right"]30/9/2019[/TD]
[TD="align: right"]31/10/2020[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]31/12/2018[/TD]
[TD="align: right"]60667[/TD]
[TD="align: right"]61152[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]31/1/2019[/TD]
[TD="align: right"]43996[/TD]
[TD="align: right"]52628[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]28/2/2019[/TD]
[TD="align: right"]6563[/TD]
[TD="align: right"]36180[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]31/3/2019[/TD]
[TD="align: right"]67431[/TD]
[TD="align: right"]65401[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]30/4/2019[/TD]
[TD="align: right"]62530[/TD]
[TD="align: right"]31532[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]31/5/2019[/TD]
[TD="align: right"]25130[/TD]
[TD="align: right"]58111[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]30/6/2019[/TD]
[TD="align: right"]21161[/TD]
[TD="align: right"]20392[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]31/7/2019[/TD]
[TD="align: right"]72953[/TD]
[TD="align: right"]29917[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]31/8/2019[/TD]
[TD="align: right"]45347[/TD]
[TD="align: right"]40353[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]30/9/2019[/TD]
[TD="align: right"]47088[/TD]
[TD="align: right"]19899[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]31/10/2019[/TD]
[TD="align: right"]12007[/TD]
[TD="align: right"]31616[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]30/11/2019[/TD]
[TD="align: right"]20347[/TD]
[TD="align: right"]63457[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]31/12/2019[/TD]
[TD="align: right"]64445[/TD]
[TD="align: right"]30394[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]31/1/2020[/TD]
[TD="align: right"]51343[/TD]
[TD="align: right"]42754[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]29/2/2020[/TD]
[TD="align: right"]17912[/TD]
[TD="align: right"]40385[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]31/3/2020[/TD]
[TD="align: right"]42382[/TD]
[TD="align: right"]56656[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]30/4/2020[/TD]
[TD="align: right"]11876[/TD]
[TD="align: right"]48569[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]31/5/2020[/TD]
[TD="align: right"]37084[/TD]
[TD="align: right"]68953[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]30/6/2020[/TD]
[TD="align: right"]19998[/TD]
[TD="align: right"]71211[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]31/7/2020[/TD]
[TD="align: right"]64036[/TD]
[TD="align: right"]50609[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="align: right"]31/8/2020[/TD]
[TD="align: right"]69359[/TD]
[TD="align: right"]45469[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="align: right"]30/9/2020[/TD]
[TD="align: right"]49787[/TD]
[TD="align: right"]34811[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]
[TD="align: right"]31/10/2020[/TD]
[TD="align: right"]10979[/TD]
[TD="align: right"]24733[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]
[TD="align: right"]30/11/2020[/TD]
[TD="align: right"]48152[/TD]
[TD="align: right"]70693[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</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