# 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
400-4.030.00222Realized Profit/Loss-19.45Profit/Loss of all closed trades-4.03
500-4.040.00222Gross profit33.96Total profit of all won trades-8.07
600-3.650.00201Gross loss53.41Total loss of all lost trades-7.69
80022.82-0.01762Profit factor0.635836Gross profit / gross loss22.82
200000Max. consecutive wins2Longest winning streak (number of trades)0
210000Max. consecutive losses4Longest losing streak (number of trades)0
220000Max. consecutive profit33.96Largest winning streak (total profit)0
230000Max. consecutive loss-38.05Largest losing streak (total loss)0
240000Paid commissions00
2500000
2600000
2700000
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

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

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

Replies
25
Views
2K
Replies
2
Views
92
Replies
11
Views
176
Replies
2
Views
102
Replies
6
Views
155

### Forum statistics

1,144,612
Messages
5,725,315
Members
422,610
Latest member
sanantonio ### 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