The formula
Quantity (crypto, 4 decimals)ExcelGoogle Sheets
=ROUNDDOWN(B1*B2/ABS(B3-B4),4)In D1 → 0.1612
Whole sharesExcelGoogle Sheets
=ROUNDDOWN(B1*B2/ABS(B3-B4),0)In D2 → 0
Position valueExcelGoogle Sheets
=D1*B3In D3 → $9,994.40
Amount at riskExcelGoogle Sheets
=D1*ABS(B3-B4)In D4 → $249.86
✓ Tested. All 4 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 | Account | 25,000 | 0.1612 | |
| 2 | Risk per trade | 1% | 0 | |
| 3 | Entry | 62,000 | $9,994.40 | |
| 4 | Stop | 60,450 | $249.86 |
How it works
- B1*B2
- Money at risk: $25,000 × 1% = $250.
- ABS(B3-B4)
- Stop distance per unit ($1,550); ABS makes it work for shorts too.
- ROUNDDOWN(…,4)
- Round down so you never risk more than planned. Use 0 for whole shares.
Common errors and fixes
Why does the formula return #DIV/0!?
Entry equals stop. Every trade needs a stop distance.
Whole shares = 0?
The stop distance is bigger than your risk budget per unit. Lower the price per unit or widen your risk.
Fees ignored?
Add round-trip fees to the stop distance for a stricter size.
Does this work in Google Sheets?
Yes. The formulas on this page work the same way in Google Sheets.