Matching +ve and -ve numbers

[andrewmurray86]

Hi all,

I'm wondering if you might help me here. I am trying to match the positive/negative numbers in a set of data. I need the cells to match. However due to the formulas used to calculate the base value it can't be as simple as *-1

Essentially if O4 is positive then p4 must also be positive, if o4 is negative then p4 must also be negative.

Copy of Andrew_Murray_Trade_Results_2020-1.xlsx
	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1
2
3	Taxes	Swap	Profit	Pips		Updated when trade is closed						-0.00222
4	0	0	-4.03	0.00222		Realized Profit/Loss		-19.45	Profit/Loss of all closed trades				-4.03
5	0	0	-4.04	0.00222		Gross profit		33.96	Total profit of all won trades				-8.07
6	0	0	-3.65	0.00201		Gross loss		53.41	Total loss of all lost trades				-7.69
7	0	0	-3.64	0.00201		Expected payoff		-0.07843	Average trade outcome				-7.29
8	0	0	22.82	-0.01762		Profit factor		0.635836	Gross profit / gross loss				22.82
9	0	0	11.14	-0.00861		Number of closed trades			8				33.96
10	0	0	-19.02	-0.01469		Profit trades			2				-19.02
11	0	0	-19.03	-0.01469		Loss trades			6				-38.05
12	0	0	0	0		Largest profit trade			22.82				0
13	0	0	0	0		Largest loss trade			-19.03				0
14	0	0	0	0		Average profit trade			$16.98				0
15	0	0	0	0		Average loss trade			-$8.90				0
16	0	0	0	0		Long trades			2				0
17	0	0	0	0		Short trades			6				0
18	0	0	0	0		Long trades won			0				0
19	0	0	0	0		Short trades won			2				0
20	0	0	0	0		Max. consecutive wins		2	Longest winning streak (number of trades)				0
21	0	0	0	0		Max. consecutive losses		4	Longest losing streak (number of trades)				0
22	0	0	0	0		Max. consecutive profit		33.96	Largest winning streak (total profit)				0
23	0	0	0	0		Max. consecutive loss		-38.05	Largest losing streak (total loss)				0
24	0	0	0	0		Paid commissions		0					0
25	0	0	0	0									0
26	0	0	0	0									0
27	0	0	0	0									0
EURNZD

Cell Formulas
Range	Formula
X3	X3	=IF(O4>0,P4,P4*-1)
M4:M27	M4	=IFERROR(IF([@Commission]="","",INDEX(Splash!L:L,MATCH($A4,Splash!$O:$O,0))),0)
N4:N27	N4	=IFERROR(IF([@Taxes]="","",INDEX(Splash!M:M,MATCH($A4,Splash!$O:$O,0))),0)
O4:O27	O4	=IFERROR(IF([@Swap]="","",INDEX(Splash!N:N,MATCH($A4,Splash!$O:$O,0)))-[@Commission],0)
P4:P27	P4	=IFERROR(([@[Close Price]]-[@[Open Price]]),0)
T4	T4	=SUM(O2:O250)
T5	T5	=SUMIF(O4:O252,">0")
T6	T6	=SUMIF($O$3:$O$252,"<0")*-1
T7	T7	=AVERAGE(O4:O251)
T8	T8	=T5/T6
U9	U9	=COUNTIF(B4:B254, ">0")
U10	U10	=COUNTIF(O4:O254,">0")
U11	U11	=COUNTIF(O4:O254,"<0")
U12	U12	=MAX(O4:O254)
U13	U13	=MIN(IF(O4:O254<0,MIN(O4:O254),O4:O254))
U14	U14	=AVERAGEIF(O4:O254, ">0")
U15	U15	=AVERAGEIF(O4:O254, "<0")
U16	U16	=COUNTIF(D4:D254, "buy")
U17	U17	=COUNTIF(D4:D254, "sell")
U18	U18	=COUNTIFS(D4:D254, "buy", O4:O254, ">0")
U19	U19	=COUNTIFS(D4:D254, "sell", O4:O254, ">0")
T20	T20	=MAX(FREQUENCY(IF(O4:O254>0,ROW(O4:O254)),IF(O4:O254<=0,ROW(O4:O254))))
T21	T21	=MAX(FREQUENCY(IF(O4:O254<0,ROW(O4:O254)),IF(O4:O254>=0,ROW(O4:O254))))
T22	T22	=MAX(Y4:Y254)
T23	T23	=MIN(Y4:Y254)
T24	T24	=SUM(L4:L254)
Y4	Y4	=O4
Y5:Y27	Y5	=IF(AND(O5>0,O4>0),IF(O5>0,O5+O4),IF(AND(O5<0,O4<0),O4+O5,O5))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Snakehips]

Does this help for P4 ...

=IFERROR((([@[Close Price]]-[@[Open Price]]))*IF(SIGN(O4)=SIGN(([@[Close Price]]-[@[Open Price]])),1,-1),0)

 

[gaz_chops]

Maybe use =sign

=SIGN(O4)*formula in P4

like

=IFERROR(sign(O4)*([@[Close Price]]-[@[Open Price]]),0)

 

[andrewmurray86]

Thank bud, that's a brilliant solution.

 

[gaz_chops]

Not sure which you went for, but both should work.

And you're welcome, thanks for the feedback