Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Round a price to end in 900

0

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

Marko

Answer
Discuss

Discussion

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: 1989) Nov 20, '18 at 5:14 pm
Add to Discussion

Answers

0

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

=ROUND(AF2,-3)-100

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.

=ROUND(AF2+100,-3)-100

Test both formulas with AF2 = 1499 or AF2 = 1500

Discuss


Answer the Question

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