Round retail prices to end in .99 or .95 (charm pricing formulas)

Nearest .99 vs margin-safe upward .99 rounding

ROUND(Price + 0.01, 0) - 0.01 shifts any calculated price to the nearest .99 ending (24.32 rounds to 24 - 0.01 = 23.99, while 24.65 rounds to 25 - 0.01 = 24.99).

If 24.32 is a minimum margin floor that you must never undercut, CEILING.MATH(Price + 0.01, 1) - 0.01 rounds upward to 24.99 while leaving an already-exact 24.99 unchanged.

Charm price ending formulas (.99 and .95)

For price points spaced five dollars apart and ending in .95, shift the input by .05 before rounding: ROUND((Price + .05)/5, 0)*5-.05. This uses the midpoint between actual price points.

=ROUND(A2 + 0.01, 0) - 0.01

Margin-safe upward .99 formula

Adding 0.01 before CEILING.MATH(..., 1) prevents 24.99 from jumping to 25.99.

=CEILING.MATH(A2 + 0.01, 1) - 0.01

Inputs and interpretation

Use nonnegative prices in a two-decimal currency and enforce your minimum price separately; low inputs can otherwise produce -.01 or -.05. Nearest rounding may reduce a price; only the upward variant preserves an input floor. Binary floating-point arithmetic can affect exact boundary values, so use controlled cent inputs and verify boundary cases.

Further reading

• Microsoft Support — CEILING.MATH function