One Formula for R Across Longs and Shorts
Goal: a single column that converts any trade into an R multiple, from the entry, stop, and exit prices.
Layout. Column A direction, "L" or "S". Column B entry. Column C stop. Column D exit. Column E for R.
Logic. Risk per unit is the distance between entry and stop. Result per unit is exit minus entry for longs, and entry minus exit for shorts. R is result divided by risk.
In E2:
=IF(A2="L",D2-B2,B2-D2)/ABS(B2-C2)
Test one. A long: entry 1.1000, stop 1.0975, exit 1.1050. Result 0.0050. Risk 0.0025. R equals 2.0.
Test two. A short: entry 1.1000, stop 1.1020, exit 1.0980. Result 0.0020. Risk 0.0020. R equals 1.0.
Test three, a loss that hit the stop: long, entry 1.1000, stop 1.0975, exit 1.0975. Result minus 0.0025. R equals minus 1.0.
Test four, a stop that slipped: long, same entry and stop, exit 1.0965. Result minus 0.0035. R equals minus 1.4. This is how slippage and widened stops show up: a loss larger than 1R.
Add a flag column: =IF(E2<-1.05,"CHECK","") to mark any loss beyond the plan by more than 5 percent.
Because the formula uses prices and not pips, it works for yen pairs, gold, and indices without changes.
Use the plan's stop, not the stop you ended up with, in column C, so that stop widening is visible.
Exercise: recompute R for your last ten trades from raw prices, and compare with your records.
One Formula for R Across Longs and Shorts
One Formula for R Across Longs and Shorts
It’s Fairman 
-
FXS HongKong Scalper
- Posts: 543
- Joined: Sat Oct 24, 2026 10:15 pm
Re: One Formula for R Across Longs and Shorts
Computing risk as the absolute difference between entry and stop, and signing the result by direction, is what makes one formula work for both sides. One edge case worth handling: a stop moved to entry gives zero risk and a division error, so the original stop should be stored in its own column and never overwritten.
FXS Official Team | Hong Kong Session Desk
-
Amanhasnoname
- Posts: 2060
- Joined: Fri Oct 30, 2026 9:45 am
Re: One Formula for R Across Longs and Shorts
I built nearly the same sheet after realising my journal had been recording shorts with the wrong sign for weeks without me noticing. My old version calculated pips and then divided by a fixed stop size I typed in by hand, so every time I adjusted a stop mid-trade the R value quietly lied to me. Switching to raw prices like this fixed both problems at once.
The breakeven edge case mentioned in the reply caught me too. I had a column of division errors for every trade where I'd dragged the stop to entry, and for a while I just deleted those rows, which made my stats look far better than they were. Now I keep a column for the planned stop that I fill in before the trade and never edit, and a separate column for where the stop actually was at exit.
One small addition that helped me was a column for the trade's open time, then a pivot table of average R by hour. On gold it showed me that my trades in the first half hour after the New York open averaged well below my overall figure, so I simply stopped trading that window.
The breakeven edge case mentioned in the reply caught me too. I had a column of division errors for every trade where I'd dragged the stop to entry, and for a while I just deleted those rows, which made my stats look far better than they were. Now I keep a column for the planned stop that I fill in before the trade and never edit, and a separate column for where the stop actually was at exit.
One small addition that helped me was a column for the trade's open time, then a pivot table of average R by hour. On gold it showed me that my trades in the first half hour after the New York open averaged well below my overall figure, so I simply stopped trading that window.