One more formula for Phd project! Fairwinds?

arnolju

New Member
Joined
Nov 27, 2005
Messages
9
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. :biggrin:
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
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
ABCDE
1Amount USDCHF-USDAMOUNtSUCCESS
21.616 
3n/a 
4n/a 
51.621 
635START1.634 
71.6281 
81.6315 
9401.6192 
10n/a 
11n/a 
12100END1.6141750
131.615 
141.6135 
151.6175 
161.6215 
Sheet1
 

arnolju

New Member
Joined
Nov 27, 2005
Messages
9
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

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
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

New Member
Joined
Nov 27, 2005
Messages
9
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!!!
 

Watch MrExcel Video

Forum statistics

Threads
1,118,288
Messages
5,571,321
Members
412,382
Latest member
Langtn02
Top