How to Round Off to the Nearest 5 Cents in Excel

Cash rounding is in use in many countries where retail prices are rounded off to the nearest 5 cents, usually because the 1c and 2c coins are no longer in circulation. The first country to do this was Sweden, back in 1972 when 1 and 2 öre coins went out of circulation: this is why this kind of rounding is sometimes also called Swedish Rounding (öresavrundning)

How to do easily do 5-cent cash rounding in Excel?

Just use the regular Excel ROUND function but divide the amount by 5 before rounding to 2 decimal places then multiply the resulting amount by 5 after the rounding.

Like this

This works because you are reducing the number of possible positions to be rounded to by a factor of 5,  then after the rounding is done, putting back the required factor of 5.

This method can generalised to round by 2c, 10c, 20c or any amount. Just replace the two 5’s in the formula with the number you need.

Use ROUND, ROUNDUP or ROUNDDOWN to give the behaviour you are looking for.

Close Menu