#### andrewmurray86

##### New Member

- Joined
- Jun 18, 2020

- Messages
- 27

- Office Version
- 2016

- Platform
- Windows

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