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:
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

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!!!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,057
Messages
5,835,161
Members
430,343
Latest member
t0m_c

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top