1 Amount USD CHF - USD AMOUNt SUCCESS
2 1.616
3 n/a
4 n/a
5 1.621
6 35 START 1.634
7 1.6281
8 1.6315
9 40 1.6192
10 n/a
11 n/a
12 100 END 1.614 175 0
13 1.615
14 1.6135
15 1.6175
16 1.6215
17 n/a
18 n/a
19 1.631
20 200 START 1.6317
21 1.6285
22 50 1.6407
23 1.6745
24 n/a
25 100 n/a
26 1.6886
27 1.6755
28 250 END 1.6743 600 1
29 1.697
30 1.7147
31
The data above is central bank intervention numbers. I have omitted the leftmost column with the dates. Amount represents intervention and START and END represent start and end of intervention event. The next column is the exchange rate with n/a indicating weekends. Total amount is the total amount of intervention during an event. The final column is the one that needs a formula. At the moment there is a formula that i enter manually next to row with "END":
=IF(AND((C14-C12)>(C6-C2),D12>0),1,IF(AND((C14-C12)<(C6-C2),D12<0),1,0))
i.e. the above is: if (E>0 and ∆sa>∆sb) or ((E<0 and ∆sa<∆sb)
where E is total amount of intervention and ∆sa is the change in CHF-USD 2 business days after and ∆sb is the change in CHF-USD 2 business days before the event. What is the problem is getting excel to pick up the change in exchange rate 2 business days after the end of the event and comparing it to the business 2 days change before the start of the event, i.e. if the second day hits n/a value like C4 then it needs to go up till C2 to get 2 days before the event and 2 days after the event. There is a whole column and many different sheets with this information so this formula needs to be dragged down so that it fills in the success of the events values each time it identifies a new event which is recognised by "START" and "END" in column B which has already been automated.
I will be most grateful to anyone who could help me out with this.
2 1.616
3 n/a
4 n/a
5 1.621
6 35 START 1.634
7 1.6281
8 1.6315
9 40 1.6192
10 n/a
11 n/a
12 100 END 1.614 175 0
13 1.615
14 1.6135
15 1.6175
16 1.6215
17 n/a
18 n/a
19 1.631
20 200 START 1.6317
21 1.6285
22 50 1.6407
23 1.6745
24 n/a
25 100 n/a
26 1.6886
27 1.6755
28 250 END 1.6743 600 1
29 1.697
30 1.7147
31
The data above is central bank intervention numbers. I have omitted the leftmost column with the dates. Amount represents intervention and START and END represent start and end of intervention event. The next column is the exchange rate with n/a indicating weekends. Total amount is the total amount of intervention during an event. The final column is the one that needs a formula. At the moment there is a formula that i enter manually next to row with "END":
=IF(AND((C14-C12)>(C6-C2),D12>0),1,IF(AND((C14-C12)<(C6-C2),D12<0),1,0))
i.e. the above is: if (E>0 and ∆sa>∆sb) or ((E<0 and ∆sa<∆sb)
where E is total amount of intervention and ∆sa is the change in CHF-USD 2 business days after and ∆sb is the change in CHF-USD 2 business days before the event. What is the problem is getting excel to pick up the change in exchange rate 2 business days after the end of the event and comparing it to the business 2 days change before the start of the event, i.e. if the second day hits n/a value like C4 then it needs to go up till C2 to get 2 days before the event and 2 days after the event. There is a whole column and many different sheets with this information so this formula needs to be dragged down so that it fills in the success of the events values each time it identifies a new event which is recognised by "START" and "END" in column B which has already been automated.
I will be most grateful to anyone who could help me out with this.