# Matching +ve and -ve numbers

#### andrewmurray86

##### New Member
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.

MNOPQRSTUVWXYZ
1
2
-0.00222
4
0
0
-4.03
0.00222
Realized Profit/Loss
-19.45
-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
-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
8
33.96
10
0
0
-19.02
-0.01469
2
-19.02
11
0
0
-19.03
-0.01469
6
-38.05
12
0
0
0
0
22.82
0
13
0
0
0
0
-19.03
0
14
0
0
0
0
\$16.98
0
15
0
0
0
0
-\$8.90
0
16
0
0
0
0
2
0
17
0
0
0
0
6
0
18
0
0
0
0
0
0
19
0
0
0
0
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")
U17U17=COUNTIF(D4:D254, "sell")
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.

### 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
Does this help for P4 ...

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

• andrewmurray86

#### gaz_chops

##### Well-known Member
Maybe use =sign

=SIGN(O4)*formula in P4

like

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

#### andrewmurray86

##### New Member
Thank bud, that's a brilliant solution.

#### gaz_chops

##### Well-known Member
Not sure which you went for, but both should work.

And you're welcome, thanks for the feedback