The formula
Win rateExcelGoogle Sheets
=COUNTIF(B2:B9,">0")/COUNT(B2:B9)In D2 → 50%
Average winExcelGoogle Sheets
=AVERAGEIF(B2:B9,">0")In D3 → $367.50
Average lossExcelGoogle Sheets
=AVERAGEIF(B2:B9,"<0")In D4 → -$137.50
Expectancy per tradeExcelGoogle Sheets
=AVERAGE(B2:B9)In D5 → $115.00
Profit factorExcelGoogle Sheets
=SUMIF(B2:B9,">0")/-SUMIF(B2:B9,"<0")In D6 → 2.67
✓ Tested. All 5 Excel results in this example were recalculated in LibreOffice Calc 25.2 and matched the values shown.
Worked example
Paste the data below into a blank sheet starting at cell A1 (or download the cheat sheet, where every example is set up). Highlighted cells hold the formulas and show the expected results.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Trade | P&L | ||
| 2 | 1 | 320 | 50% | |
| 3 | 2 | -150 | $367.50 | |
| 4 | 3 | -90 | -$137.50 | |
| 5 | 4 | 410 | $115.00 | |
| 6 | 5 | -200 | 2.67 | |
| 7 | 6 | 180 | ||
| 8 | 7 | -110 | ||
| 9 | 8 | 560 |
How it works
- COUNTIF(…,">0")/COUNT(…)
- Winning trades ÷ all trades.
- AVERAGE(B2:B9)
- Expectancy = win rate × avg win + loss rate × avg loss, which equals the plain average P&L.
- SUMIF(…,">0")/-SUMIF(…,"<0")
- Gross profit ÷ gross loss. Above 1 = profitable.
Common errors and fixes
Why does the formula return #DIV/0!?
No losing trades yet (profit factor) or no trades.
Break-even trades?
A 0 counts in COUNT but not as a win. Decide how to treat them.
Does this work in Google Sheets?
Yes. The formulas on this page work the same way in Google Sheets.