Calculate Value Between Two Years

[kumara_faith]

Hi,

I have an input sheet as below:

Book2
	D	E
3	Date	Sales
4	14/7/2010	100
5	15/7/2010	50
6	18/7/2011	200
7	19/7/2011	300
8	20/7/2011	840
9	21/7/2011	200
10	22/7/2011	100
11	25/7/2011	600
12	17/2/2012	250
13	21/2/2012	210
14	22/2/2012	240
15	1/4/2013	540
16	2/4/2013	540
17	3/4/2013	540
18	4/4/2013	560
19	5/4/2013	800
Sheet1

In another table, I have the start year and end year and the growth value between these two years. I am trying to calculate the growth of the sales amount between the first date of the start year and the last date of the end year..

Book2
	H	I	J
3	Start Year	End Year	Growth
4	2010	2011	#N/A
Sheet1

Cell Formulas
Range	Formula
J4	J4	=XLOOKUP(I4,D4:D19,E4:E19)/XLOOKUP(H4,D4:D19,E4:E19)-1

My current formula does not seem to work. Since the first sales amount in year 2010 is 100 and the last sales amount in year 2011 is 600, therefore the correct growth value should be 500%. Appreciate all the help.

 

[Fluff]

How about

=XLOOKUP(DATE(I4,12,31),D4:D19,E4:E19,,-1)/XLOOKUP(DATE(H4,1,1),D4:D19,E4:E19,,1)-1

 

[kumara_faith]

Hi Fluff,

That worked. Thank you for your valuable time and patience. Have a great day?

 

[Fluff]

You're welcome & thanks for the feedback.