andrewmurray86
New Member
 Joined
 Jun 18, 2020
 Messages
 29
 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.
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_20201.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. 