Round a price to end in 900


Hello. Im trying to use Round function to round prices to end 900. I have 5 or 6 digit numbers without decimal ie ( 23450, 234500). When trying to use formula: =ROUND(AF2,0)-0.01 excel give me an error:

Not trying to type a formula?

When the first character is an equal (=) or minus (-) sign, Excel thinks it's a formula:

• you type:   =1+1, cell shows:   2

To get around this, type an apostrophe ( ' ) first:

• you type:   '=1+1, cell shows:   =1+1

I need help with this formula. Tried all options.

Thank you




Please don't forget to select an answer if it worked for you! Just click the Select Answer button at the bottom of the desired answer.
don (rep: 1665) Nov 20, '18 at 5:14 pm
Add to Discussion



The function below will always produce a number ending on 900 provided that AF2 is 900 or larger.


You may prefer to embed it in an IF statement if you need to handle smaller numbers. However, the rounding will not be perfect. Perhaps a variation of the above as shown next will produce a more accurate result. I haven´t tested the logic. Just showing how you might get a better rounding if that is an issue for you.


Test both formulas with AF2 = 1499 or AF2 = 1500


Answer the Question

You must create an account to use the forum. Create an Account or Login