A client recently requested a formula to round to the nearest 9 cents. This avoids getting to a price point. This is a common requirement in retail businesses. The solution was simpler than I thought it would be.

You might think you need to use the IF function to handle this, but you can just use the ROUND function.

In the image below you can see values in column A and the rounded values to the nearest nine cents in column B. The formula in column B is shown in column C.

The technique involves rounding to the nearest 10 cents, or one decimal place. Then deducting one cent.

This rounds numbers that end in 4 cents down, rather than up.

If you want to round a 4 cent value up to the nearest nine cents then you can use an alternative formula shown in the image below.

This adds 1 cent to the value and then rounds to one decimal place and then deducts the 1 cent.

Given this will affect margins you may want to use this second formula.

You can see the differences on rows 14 and 24 in the image above.