# One more formula for Phd project! Fairwinds?

#### arnolju

##### New Member
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.

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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.

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

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?

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.

Thank you fairwinds!

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

Replies
18
Views
1K
Replies
4
Views
592
Replies
3
Views
941
Replies
13
Views
713
Replies
6
Views
2K

1,212,096
Messages
6,105,941
Members
447,985
Latest member
gigi11

### 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.

### Which adblocker are you using?

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

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