Matching +ve and -ve numbers

andrewmurray86

New Member
Joined
Jun 18, 2020
Messages
27
Office Version
2016
Platform
Windows
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
MNOPQRSTUVWXYZ
1
2
3TaxesSwapProfitPipsUpdated 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
RangeFormula
X3X3=IF(O4>0,P4,P4*-1)
M4:M27M4=IFERROR(IF([@Commission]="","",INDEX(Splash!L:L,MATCH($A4,Splash!$O:$O,0))),0)
N4:N27N4=IFERROR(IF([@Taxes]="","",INDEX(Splash!M:M,MATCH($A4,Splash!$O:$O,0))),0)
O4:O27O4=IFERROR(IF([@Swap]="","",INDEX(Splash!N:N,MATCH($A4,Splash!$O:$O,0)))-[@Commission],0)
P4:P27P4=IFERROR(([@[Close Price]]-[@[Open Price]]),0)
T4T4=SUM(O2:O250)
T5T5=SUMIF(O4:O252,">0")
T6T6=SUMIF($O$3:$O$252,"<0")*-1
T7T7=AVERAGE(O4:O251)
T8T8=T5/T6
U9U9=COUNTIF(B4:B254, ">0")
U10U10=COUNTIF(O4:O254,">0")
U11U11=COUNTIF(O4:O254,"<0")
U12U12=MAX(O4:O254)
U13U13=MIN(IF(O4:O254<0,MIN(O4:O254),O4:O254))
U14U14=AVERAGEIF(O4:O254, ">0")
U15U15=AVERAGEIF(O4:O254, "<0")
U16U16=COUNTIF(D4:D254, "buy")
U17U17=COUNTIF(D4:D254, "sell")
U18U18=COUNTIFS(D4:D254, "buy", O4:O254, ">0")
U19U19=COUNTIFS(D4:D254, "sell", O4:O254, ">0")
T20T20=MAX(FREQUENCY(IF(O4:O254>0,ROW(O4:O254)),IF(O4:O254<=0,ROW(O4:O254))))
T21T21=MAX(FREQUENCY(IF(O4:O254<0,ROW(O4:O254)),IF(O4:O254>=0,ROW(O4:O254))))
T22T22=MAX(Y4:Y254)
T23T23=MIN(Y4:Y254)
T24T24=SUM(L4:L254)
Y4Y4=O4
Y5:Y27Y5=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.
 

Some videos you may like

Excel Facts

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

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,050
Office Version
2013
Platform
Windows
Does this help for P4 ...

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

gaz_chops

Well-known Member
Joined
Apr 29, 2003
Messages
6,322
Platform
MacOS
Maybe use =sign

=SIGN(O4)*formula in P4

like

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

gaz_chops

Well-known Member
Joined
Apr 29, 2003
Messages
6,322
Platform
MacOS
Not sure which you went for, but both should work.

And you're welcome, thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,100,039
Messages
5,472,121
Members
406,805
Latest member
AlesD6

This Week's Hot Topics

Top