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