Dynamic ranges & fancy lookup/index & match - I think?

[BruceP]

I have a data set of exchange rates that change over time (every week). Date in the first column, the exchange rate in the second and in the third column I have calculated the percentage change relative to the first week - let's call this the 'base week'.

In one cell I have entered a variable which is a percentage (this is named Tolerance) and in another a variable that indicates a number of weeks - I call this value ConsecWeeks.

My challenge: when the percentage change exceeds more than the tolerance for more than the number of ConsecWeeks I want to change the 'base week' exchange rate to that week's exchange rate we are now currently at and calculate the following weeks' percentage change with this new 'base week' exchange rate until the tolerance is exceeded again for the number of ConsecWeeks and then change the base week exchange rate to the value at this new date and so on.

I've worked out a formula that counts the number of weeks that exceed the tolerance using the Tolerance variable and the ConsecWeeks variable

Tolerance is in cell D1, currently set to 2%, and ConsecWeeks is in D2, currently set to 2.

The data headings are in row 4

Here's some sample data.

Date EUR/GBP ABS % change Consecutive weeks of change
01/12/2013 0.8333 0 0
08/12/2013 0.8310 0.28% 0
15/12/2013 0.8152 2.17% 1
22/12/2013 0.8112 2.65% 2
29/12/2013 0.8352 0.23% 2
05/01/2014 0.8308 0.30% 1
12/01/2014 0.8285 0.58% 0
19/01/2014 0.8292 0.49% 0
26/01/2014 0.8248 1.02% 0
02/02/2014 0.7989 4.13% 1
09/02/2014 0.7921 4.94% 2
16/02/2014 0.8232 1.21% 2
23/02/2014 0.8234 1.19% 1
02/03/2014 0.8235 1.18% 0
09/03/2014 0.8260 0.88% 0
16/03/2014 0.8345 0.14% 0
23/03/2014 0.8364 0.37% 0
30/03/2014 0.8310 0.28% 0

The formula that calculates the percentage change from C6 and down the column is =ABS((B6-$B$5)/$B$5)

The formula that counts the number of weeks that the tolerance percentage has been exceeded is =COUNTIF(OFFSET(INDIRECT("R"&ROW()-$D$2&"C"&COLUMN()-1,FALSE),0,0,$D$2+1,1),">"&$D$1)

So, in the above we start with the exchange rate at 0.8333. By the 22/12/2013 we've had two consecutive weeks where the exchange rate has been 2 percent more or less than when we started on 1/12/2013 so from 29/12/2013 I would like the base exchange rate to be 0.8352. The next change would need to happen on 16/02/2014.

I can write the logic down in a crude fashion that says what I want to happen, I just can't work out the formulas needed to achieve it!

I am sure some VBA would be able to achieve this relatively easily but I only have a very basic understanding of VBA and no where near enough to write something that could tackle this.

Is what I want to achieve doable with formulas? I think some form of look up with dynamic ranges is what is needed aided by some helper columns?

My thanks in advance for any comments, suggestions and solutions.

 

[J.Ty.]

Hi,

Is the following what you want? Note that the formulas from row 6 are used to fill the spreadsheet. Row 5 is used for initialization.

J.Ty.

Excel 2010
	A	B	C	D	E	F	G
1
2
3
4	Date	EUR/GBP	ABS % change	Consecutive weeks of change	Tolerance	ConsecWeeks	Base
5	01/12/2013	0.8333	0.00%	0	1.00%	3	0.8333
6	08/12/2013	0.831	0.28%	0			0.8333
7	15/12/2013	0.8152	2.18%	1			0.8333
8	22/12/2013	0.8112	2.71%	2			0.8333
9	29/12/2013	0.8352	0.23%	0			0.8333
10	05/01/2014	0.8308	0.30%	0			0.8333
11	12/01/2014	0.8285	0.58%	0			0.8333
12	19/01/2014	0.8292	0.49%	0			0.8333
13	26/01/2014	0.8248	1.03%	1			0.8333
14	02/02/2014	0.7989	4.17%	2			0.8333
15	09/02/2014	0.7921	5.16%	3			0.8333
16	16/02/2014	0.8232	1.28%	4			0.8232
17	23/02/2014	0.8234	0.02%	0			0.8232
18	02/03/2014	0.8235	0.04%	0			0.8232
19	09/03/2014	0.826	0.34%	0			0.8232
20	16/03/2014	0.8345	1.37%	1			0.8232
21	23/03/2014	0.8364	1.58%	2			0.8232
22	30/03/2014	0.831	0.93%	0			0.8232
Sheet1

Cell Formulas
Range	Formula
D5	=0
D6	=IF(C6>$E$5,1+D5,0)
C6	=ABS((B6-G5)/B5)
G5	=B5
G6	=IF(D6>$F$5,B6,G5)

 

[BruceP]

Thank you so much. My apologies for not getting back to you sooner. Your solution works perfectly and is far simpler than what I imagined it needed to be.

 

[J.Ty.]

Thanks for the feedback and good luck!
J.Ty.