Array formula to compare values between two dates

Graemea

Board Regular
Joined
Oct 30, 2015
Messages
119
Office Version
  1. 365
Platform
  1. Windows
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!
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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
ABCDEFGH

<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

 
Upvote 0
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
ABCDE
1WE DateProduct AProduct B9/30/201910/31/2020
212/31/20186066761152
31/31/2019439965262842.86%
42/28/2019656336180
53/31/20196743165401
64/30/20196253031532
75/31/20192513058111
86/30/20192116120392
97/31/20197295329917
108/31/20194534740353
11437384708819899
12437691200731616
13437992034763457
14438306444530394
151/31/20205134342754
162/29/20201791240385
173/31/20204238256656
184/30/20201187648569
195/31/20203708468953
206/30/20201999871211
217/31/20206403650609
228/31/20206935945469
239/30/20204978734811
2410/31/20201097924733
2511/30/20204815270693
Sheet
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top