Exclusion formula

[andrewmurray86]

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
AUDCAD
AUDCAD
AUDNZD
AUDNZD

The common value there being AUD, had the trade been AUDCAD and EURNZD, the value next to both would be 1
Again, if the trade was AUDCAD and USDCAD (instead of AUDNZD) then +1 for AUDCAD (since it came first) and -1 for USDCAD (since it came second)

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1			OPEN					CLOSE		Profit	Pips		RAW	Filter	Filter
2	10000												10000	10000				STREAK
3	10094.50		2018-01-02 00:00:08	1.20112	1.19482	1.19482	1.20742	2018-01-02 12:00:56	1.20742	94.50	63.0	EURUSD	10094.50	10094.50	94.50			94.50
4	10124.50		2018-01-02 00:00:08	1.20112	1.20312	1.19482		2018-01-03 10:07:36	1.20312	30.00	20.0	EURUSD	10124.50	10124.50	30.00			124.5
5	10124.50	0.00%	2018-01-08 00:00:43	0.97584	0.98084	0.98084		2018-01-10 21:08:04	0.98084	-100.00	-50.0	AUDCAD	10024.50	10023.26	-101.25			0
6	10124.50	0.99%	2018-01-08 00:00:43	0.97584	0.98084	0.98084	0.97084	2018-01-10 21:08:04	0.98084	-100.00	-50.0	AUDCAD	9924.50	9922.01	-101.25		0.76%	-100
7	10124.50	1.98%	2018-01-10 00:00:00	0.88149	0.88509	0.88509	0.87789	2018-01-10 12:23:37	0.88509	-97.20	-36.0	EURGBP	9827.30	9825.57	-96.44		1.73%	-197.2
8	10124.50	2.94%	2018-01-10 00:00:00	0.88149	0.88509	0.88509		2018-01-10 12:23:37	0.88509	-97.20	-36.0	EURGBP	9730.10	9729.13	-96.44		2.70%	-294.4
9	10124.50		2018-01-10 00:00:19	114.604	115.254	115.254	113.954	2018-01-10 08:40:25	113.954	96.98	65.0	CHFJPY	9827.08	9823.48	94.35		1.73%	0
10	10124.50		2018-01-10 00:00:19	114.604	114.404	115.254		2018-01-12 11:25:30	114.404	29.72	20.0	CHFJPY	9856.80	9852.39	28.91		1.43%	29.72
11	10124.50		2018-01-11 00:00:00	0.98374	0.97834	0.97834	0.98914	2018-01-11 10:18:20	0.98914	97.20	54.0	AUDCAD	9954.00	9948.16	95.77		0.46%	126.92
12	10124.50		2018-01-11 00:00:00	0.98374	0.98574	0.97834		2018-01-12 12:16:07	0.98574	36.00	20.0	AUDCAD	9990.00	9983.63	35.47		0.10%	162.92
13	10124.50	1.33%	2018-01-11 00:00:01	1.08918	1.09418	1.09418	1.08418	2018-01-11 03:08:06	1.09418	-100.00	-50.0	AUDNZD	9890.00	9883.79	-99.84		1.10%	0
14	10124.50	2.32%	2018-01-11 00:00:01	1.08918	1.09418	1.09418		2018-01-11 03:08:06	1.09418	-100.00	-50.0	AUDNZD	9790.00	9783.96	-99.84		2.10%	-100
15	10124.50		2018-01-15 00:00:04	1.21943	1.21143	1.21143	1.22743	2018-01-15 12:17:28	1.22743	96.00	80.0	EURUSD	9886.00	9877.88	93.93		1.14%	0
16	10124.50		2018-01-15 00:00:04	1.21943	1.22143	1.21143		2018-01-17 09:45:07	1.22143	24.00	20.0	EURUSD	9910.00	9901.36	23.48		0.90%	24
17	10124.50		2018-01-15 00:01:07	1.71023	1.69923	1.69923	1.72123	2018-01-17 16:59:45	1.72123	99.00	110.0	GBPCAD	10009.00	9999.39	98.02			123
18	10281.25		2018-01-15 00:01:07	1.71023	1.69923	1.69923		2018-01-29 00:03:01	1.74048	272.25	302.5	GBPCAD	10281.25	10268.95	269.56			395.25
19	10379.85		2018-01-16 00:00:00	0.98978	0.98398	0.98398	0.99558	2018-01-17 16:59:45	0.99558	98.60	58.0	AUDCAD	10379.85	10370.20	101.25			493.85
20	10400.25		2018-01-16 00:00:00	0.98978	0.98398	0.98398		2018-01-18 00:00:00	0.99098	20.40	12.0	AUDCAD	10400.25	10391.15	20.95			514.25
21	10496.25		2018-01-25 00:00:00	1.24081	1.23281	1.23281	1.24881	2018-01-25 15:45:32	1.24881	96.00	80.0	EURUSD	10496.25	10490.91	99.76			610.25
22	10514.37		2018-01-25 00:00:00	1.24081	1.23281	1.23281		2018-01-29 00:00:34	1.24232	18.12	15.1	EURUSD	10514.37	10509.74	18.83			628.37
23	10608.76		2018-01-29 00:04:07	116.216	115.366	115.366	117.066	2018-01-31 17:31:49	117.066	94.39	85.0	CHFJPY	10608.76	10608.94	99.20			722.76
24	10701.61		2018-01-29 00:04:07	116.216	115.366	115.366		2018-02-06 00:00:00	117.052	92.85	83.6	CHFJPY	10701.61	10706.52	97.58			815.61
25	10796.11		2018-02-01 00:00:00	0.99178	0.99808	0.99808	0.98548	2018-02-01 12:48:47	0.98548	94.50	63.0	AUDCAD	10796.11	10807.70	101.18			910.11
26	10861.21		2018-02-01 00:00:00	0.99178	0.99808	0.99808		2018-02-07 00:00:00	0.98744	65.10	43.4	AUDCAD	10861.21	10877.40	69.70			975.21
27	10959.21		2018-02-02 00:00:00	1.08628	1.09328	1.09328	1.07928	2018-02-06 05:36:23	1.07928	98.00	70.0	AUDNZD	10959.21	10983.99	106.60			1073.21
28	10987.21		2018-02-02 00:00:00	1.08628	1.08428	1.09328		2018-02-07 22:13:01	1.08428	28.00	20.0	AUDNZD	10987.21	11014.45	30.46			1101.21
29	10987.21	0.00%	2018-02-06 00:00:00	0.88566	0.87986	0.87986		2018-02-08 00:00:00	0.88344	-35.52	-22.2	EURGBP	10951.69	10975.33	-39.12			0
30	10987.21	0.32%	2018-02-06 00:00:00	0.88566	0.87986	0.87986	0.89146	2018-02-08 00:00:00	0.88344	-35.52	-22.2	EURGBP	10916.17	10936.20	-39.12			-35.52
31	11017.17		2018-02-07 00:00:00	1.23766	1.24776	1.24776	1.22756	2018-02-07 19:03:43	1.22756	101.00	101.0	EURUSD	11017.17	11046.66	110.46			0
AM

Cell Formulas
Range	Formula
A2	A2	=M2
A3:A31	A3	=MAX(M3,A2)
B3:B31	B3	=IF(M3 < N(M2), 1-M2/A2, "")
M3:M31	M3	=M2+J3
N3	N3	=O3+N2
O3,O31,O29,O27,O25,O23,O21,O19,O17,O15,O13,O11,O9,O7,O5	O3	=J3*(N2/$N$2)
N4:N31	N4	=N3+O4
O4,O30,O28,O26,O24,O22,O20,O18,O16,O14,O12,O10,O8,O6	O4	=J4*(N2/$N$2)
Q3:Q31	Q3	=IF(M:M<10000,(10000-M:M)*0.0001,"")
R3	R3	=J3
R4:R31	R4	=IF((AND(J4<0,R3<=0)),J4+R3,IF((AND(J4>0,R3>=0)),J4+R3,0))

 

[andrewmurray86]

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?