# CORRECTION - complex formulas needed

#### arnolju

##### New Member
DATE AMOUNT CHF-USD EVENT TOTAL SUCCESS 1 SUCCESS 2
22/09/86 1.637
23/09/86 1.6616
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

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Julia

Can you please give a completed example and use COLO's HTML maker to paste the example on the board.

Tony

I tried many times but my PC has been very damaged recently and it crushes when i try to use html. I guess the date column is column A, B column has blank cells and values only for some, then there is column C with all the daily exchange rates, column D has event "START" and "END" which is calculated automatically by excel, column E sums all the values between START and END of the event, the last two columns have been calculated manually thus far but need automation and need the value only next to "END" of each event.

will try to paste again using HTML but i think i will have probs again

Julia

Tony

Replies
1
Views
219
Replies
5
Views
237
Replies
3
Views
208
Replies
1
Views
178
Replies
1
Views
210

1,196,078
Messages
6,013,301
Members
441,760
Latest member
Sharina

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

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