# Apologies, another correction - formulas needed for phd

#### arnolju

##### New Member
CORRECTION - complex formulas needed
DATE AMOUNT CHF-USD EVENT TOTAL SUCCESS 1 SUCCESS 2

24/09/86 1.6575
25/09/86 1.6625
26/09/86 1.66
29/09/86 1.6425
30/09/86 1.6455
01/10/86 1.6442
02/10/86 1.6325
03/10/86 1.616
06/10/86 1.621
07/10/86 35 1.634 START
08/10/86 1.6281
09/10/86 1.6315
10/10/86 1.6192
13/10/86 1.614
14/10/86 100 1.615 END 135 1 -0.959%
15/10/86 1.6135
16/10/86 1.6175
21/10/87 1.5025
22/10/87 1.5015
23/10/87 1.4935
26/10/87 1.46
27/10/87 1.4585
28/10/87 100 1.441 START
29/10/87 130 1.429
30/10/87 1.4285
02/11/87 60 1.42
03/11/87 1.4135
04/11/87 1.4085
05/11/87 1.3825
06/11/87 30 1.376
09/11/87 1.368
10/11/87 30 1.363 END 350 1 3.062%

I need to automate the last two columns "SUCCESS - 2DAY" columns with formulas. The basic set up of the formulas is as follows:

IF (E>0 and Sa>0) or (E<0 and Sa<0) then event is success, show 1

where E = total amount = column E

Sa = change in exchange rate after the event = i.e. (1.6175 - 1.615) for 1st event.

OR

(E>0 and Sa > Sb) or (E<0 and Sa <Sb)

where Sb = change in exchange rate before the event. i.e. = (1.441 - 1.46) for the first event.

So i need to merge those two conditions above for the formula for the first success column and get it so that when i drag it down it gets filled in for every row with "END", however excel needs to understand that every EVENT will have different number of rows so it needs to pick up the "START" values and then copare before and after the events.

I need this formula to work for 2 days as shown above and also for 5 days before and after the event.

For the last column the success the formula changes for the following criteria:

instead of 1 for success or 0 for not success, i need it to show:

IF(TOTAL AMOUNT>0,((% CHANGE after the event)- (%change before the event)),((%change before the event)-(%change after the event))) - i.e. the value will be shown as percentage. Again the only complexity here is to get excel to pick up where the start of the event is.

I will be extremely grateful to anyone who could help me with this.

### Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Replies
4
Views
134
Replies
13
Views
255
Replies
5
Views
238
Replies
3
Views
210
Replies
1
Views
188

1,196,447
Messages
6,015,318
Members
441,888
Latest member
birexual

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