Math & Rounding
Round Retail Prices to End in .45 or .95 with Minimum-Margin Floor Guard
Snap raw cost-plus catalog prices to the nearest .45 or .95 psychological charm ending—or force upward rounding when nearest-step rounding would dip below your target floor price.
Shifted half-dollar lattice math for .45 and .95 charm prices
Prices ending in .45 and .95 sit on a regular 0.50 grid shifted down by 0.05 (0.45 = 0.50 - 0.05, 0.95 = 1.00 - 0.05, 1.45 = 1.50 - 0.05). Adding 0.05 to any raw price aligns it with exact 0.50 multiples, rounding (price + 0.05) * 2 to the nearest integer and dividing by 2 snaps to the closest half-dollar, and subtracting 0.05 restores the .45 / .95 charm suffix.
In merchandising schedules, nearest-step rounding can pull a raw target price like 12.18 down to 11.95—eroding contracted gross margin. Pairing nearest-charm rounding with an upward-only CEILING alternative (-INT(-(price + 0.05) * 2) / 2 - 0.05) guarantees every SKU clears its minimum margin floor.
| Raw Target Price | Nearest .45 / .95 | Ceiling .45 / .95 (No Margin Erosion) | Ending Cents Check |
|---|---|---|---|
| 12.18 | 11.95 | 12.45 | 0.95 / 0.45 |
| 12.22 | 12.45 | 12.45 | 0.45 / 0.45 |
| 19.71 | 19.95 | 19.95 | 0.95 / 0.95 |
| 8.45 | 8.45 | 8.45 | Exact charm preserved |
Nearest .45 or .95 Psychological Price Formula
Shift by +0.05, round to the nearest 0.50 increment via ROUND(x * 2, 0) / 2, and subtract 0.05 (clamping tiny prices below 0.45 to 0.45).
=MAX(0.45, ROUND((C2 + 0.05) * 2, 0) / 2 - 0.05)Margin-Safe Upward .45 or .95 Formula
When catalog rules forbid pricing below raw cost/(1 - min_margin), round upward along the shifted 0.50 lattice so 12.18 steps up to 12.45 instead of dropping to 11.95.
=MAX(0.45, -INT(-ROUND((C2 + 0.05) * 2, 6)) / 2 - 0.05)Try the example
Use the original values or change them to test your understanding.