# Exclusion formula

#### andrewmurray86

##### New Member
Hi all,

I'm hoping you might be able to help me here. What I'm hoping to do is manage system of forex trades. Some brief background which may help with solutions:
There are 28 main forex pairs based upon the following 8 major currencies: AUD, CAD, CHF, EUR, GBP, JPY, NZD, USD.

What I want to do is have a formula that will put a value in column P (either +1 or -1) based on the open time of each trade and whether or not the trade above contains a currency that is already listed. Some difficulty may be encountered in that each trade has 2 components (in column L you see that each pair is repeated below)

For example in L11-L14 you'll see that the below trades were opened on the same day. What I want to do is have a 1 next to the AUDCAD trades and a -1 next to the AUDNZD
AUDNZD
AUDNZD

The common value there being AUD, had the trade been AUDCAD and EURNZD, the value next to both would be 1

So what I'm hoping is that the formula can check if the date is the same as the trade above, and if so, add a 1 into the P column of the row the trade appears if the trade is different currencies or a -1 if the trades share a currency.

Here is the sheet

Mav 3.2 compund.xlsx
ABCDEFGHIJKLMNOPQR
1OPENCLOSEProfitPipsRAWFilterFilter
2100001000010000STREAK
310094.50 2018-01-02 00:00:081.201121.194821.194821.207422018-01-02 12:00:561.2074294.5063.0EURUSD10094.5010094.5094.50 94.50
410124.50 2018-01-02 00:00:081.201121.203121.194822018-01-03 10:07:361.2031230.0020.0EURUSD10124.5010124.5030.00 124.5
710124.501.98%2018-01-10 00:00:000.881490.885090.885090.877892018-01-10 12:23:370.88509-97.20-36.0EURGBP9827.309825.57-96.441.73%-197.2
810124.502.94%2018-01-10 00:00:000.881490.885090.885092018-01-10 12:23:370.88509-97.20-36.0EURGBP9730.109729.13-96.442.70%-294.4
910124.50 2018-01-10 00:00:19114.604115.254115.254113.9542018-01-10 08:40:25113.95496.9865.0CHFJPY9827.089823.4894.351.73%0
1010124.50 2018-01-10 00:00:19114.604114.404115.2542018-01-12 11:25:30114.40429.7220.0CHFJPY9856.809852.3928.911.43%29.72
1310124.501.33%2018-01-11 00:00:011.089181.094181.094181.084182018-01-11 03:08:061.09418-100.00-50.0AUDNZD9890.009883.79-99.841.10%0
1410124.502.32%2018-01-11 00:00:011.089181.094181.094182018-01-11 03:08:061.09418-100.00-50.0AUDNZD9790.009783.96-99.842.10%-100
1510124.50 2018-01-15 00:00:041.219431.211431.211431.227432018-01-15 12:17:281.2274396.0080.0EURUSD9886.009877.8893.931.14%0
1610124.50 2018-01-15 00:00:041.219431.221431.211432018-01-17 09:45:071.2214324.0020.0EURUSD9910.009901.3623.480.90%24
2110496.25 2018-01-25 00:00:001.240811.232811.232811.248812018-01-25 15:45:321.2488196.0080.0EURUSD10496.2510490.9199.76 610.25
2210514.37 2018-01-25 00:00:001.240811.232811.232812018-01-29 00:00:341.2423218.1215.1EURUSD10514.3710509.7418.83 628.37
2310608.76 2018-01-29 00:04:07116.216115.366115.366117.0662018-01-31 17:31:49117.06694.3985.0CHFJPY10608.7610608.9499.20 722.76
2410701.61 2018-01-29 00:04:07116.216115.366115.3662018-02-06 00:00:00117.05292.8583.6CHFJPY10701.6110706.5297.58 815.61
2710959.21 2018-02-02 00:00:001.086281.093281.093281.079282018-02-06 05:36:231.0792898.0070.0AUDNZD10959.2110983.99106.60 1073.21
2810987.21 2018-02-02 00:00:001.086281.084281.093282018-02-07 22:13:011.0842828.0020.0AUDNZD10987.2111014.4530.46 1101.21
2910987.210.00%2018-02-06 00:00:000.885660.879860.879862018-02-08 00:00:000.88344-35.52-22.2EURGBP10951.6910975.33-39.12 0
3010987.210.32%2018-02-06 00:00:000.885660.879860.879860.891462018-02-08 00:00:000.88344-35.52-22.2EURGBP10916.1710936.20-39.12 -35.52
3111017.17 2018-02-07 00:00:001.237661.247761.247761.227562018-02-07 19:03:431.22756101.00101.0EURUSD11017.1711046.66110.46 0
AM
Cell Formulas
RangeFormula
A2A2=M2
A3:A31A3=MAX(M3,A2)
B3:B31B3=IF(M3 < N(M2), 1-M2/A2, "")
M3:M31M3=M2+J3
N3N3=O3+N2
O3,O31,O29,O27,O25,O23,O21,O19,O17,O15,O13,O11,O9,O7,O5O3=J3*(N2/\$N\$2)
N4:N31N4=N3+O4
O4,O30,O28,O26,O24,O22,O20,O18,O16,O14,O12,O10,O8,O6O4=J4*(N2/\$N\$2)
Q3:Q31Q3=IF(M:M<10000,(10000-M:M)*0.0001,"")
R3R3=J3
R4:R31R4=IF((AND(J4<0,R3<=0)),J4+R3,IF((AND(J4>0,R3>=0)),J4+R3,0))

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### andrewmurray86

##### New Member
Is it possible to use a matching function? Is it possible to match based on a fluid basis? For example in column L, match based on the cell above?

Replies
4
Views
125
Replies
6
Views
123
Replies
19
Views
389
Replies
20
Views
342
Replies
3
Views
169

1,129,499
Messages
5,636,687
Members
416,935
Latest member
Atulcp

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