One more formula for Phd project! Fairwinds?

[arnolju]

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.

 

[PaddyD]

Julia,

You'd be better set up to get help with this if you downloaded the html maker addin (see faq thread at the top of the board) & used it to post up a formatted version of your data.

 

[fairwinds]

Hi again Julia!

This is formula is a little long, but as you are going for Phd...

Enter:

=IF(B2="END",IF(AND((INDEX(C4:C6,MATCH(TRUE,ISNUMBER(C4:C6),0))-C2)>(LOOKUP(2,1/($B1:B$2="START"),$C1:C$2)-LOOKUP(9.99999999999999E+307,OFFSET($C$1,MATCH(2,1/($B1:B$2="START"))-2,0,-3))),D2>0),1,IF(AND((INDEX(C4:C6,MATCH(TRUE,ISNUMBER(C4:C6),0))-C2)<(LOOKUP(2,1/($B1:B$2="START"),$C1:C$2)-LOOKUP(9.99999999999999E+307,OFFSET($C$1,MATCH(2,1/($B1:B$2="START"))-2,0,-3))),D2<0),1,0)),"")

in E2, confirm formula with Ctrl + shift + enter, not just with enter then drag down.

Book1
	A	B	C	D	E
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
Sheet1

 

[arnolju]

TO FAIRWINDS!

Thank you so much again for that. I find it very tricky to understand that formula though. How would i need to modify it if say i wanted to count 5 business days before and after the event instead of only 2?

By the way what is your job? Do you have a Phd as well?

 

[fairwinds]

The formula in E2 would then be:

=IF(B2="END",IF(AND((INDEX(C7:C9,MATCH(TRUE,ISNUMBER(C7:C9),0))-C2)>(LOOKUP(2,1/($B1:B$2="START"),$C1:C$2)-LOOKUP(9.99999999999999E+307,OFFSET($C$1,MATCH(2,1/($B1:B$2="START")) -5,0,-3))),D2>0),1,IF(AND((INDEX(C7:C9,MATCH(TRUE,ISNUMBER(C7:C9),0))-C2)<(LOOKUP(2,1/($B1:B$2="START"),$C1:C$2)-LOOKUP(9.99999999999999E+307,OFFSET($C$1,MATCH(2,1/($B1:B$2="START")) -5,0,-3))),D2<0),1,0)),"")

Changed parts are bolded.

Note that this formula would give an error on your sample data as you do not have 5 busines days before first start.

 

[arnolju]

Thank you fairwinds!

To fairwinds. Thank you very much again for taking your time and putting your effort and making things clear for me.

Your help is immensely appreciated!!!