How to roundup a number to the closest ten?

问题:

Probably the title is not very suggestive.

Let me explain you with an example. I have:

12345.6 
2345.1
12345.00000001

I want those numbers to be roundup to 12350.
How can I do this?

If possible, I would rather use formulas instead of VBA.

回答1:

You could also use CEILING which rounds up to an integer or desired multiple of significance

ie
=CEILING(A1,10)
rounds up to a multiple of 10

12340.0001 will become 12350

回答2:

Use ROUND but with num_digits = -1

=ROUND(A1,-1)

Also applies to ROUNDUP and ROUNDDOWN

From Excel help:

• If num_digits is greater than 0 (zero), then number is rounded to the specified number of decimal places.
• If num_digits is 0, then number is rounded to the nearest integer.
• If num_digits is less than 0, then number is rounded to the left of the decimal point.

EDIT: To get the numbers to always round up use =ROUNDUP(A1,-1)

回答3:

You can use the function MROUND(<reference cell>, <round to multiple of digit needed>).

Example:

1. For a value A1 = 21 round to multiple of 10 it would be written as =MROUND(A1,10) for which Result = 20

2. For a value Z4 = 55.1 round to multiple of 10 it would be written as =MROUND(Z4,10) for which Result = 60

回答4:

the second argument in ROUNDUP, eg =ROUNDUP(12345.6789,3) refers to the negative of the base-10 column with that power of 10, that you want rounded up. eg 1000 = 10^3, so to round up to the next highest 1000, use ,-3)

=ROUNDUP(12345.6789,-4) = 20,000
=ROUNDUP(12345.6789,-3) = 13,000
=ROUNDUP(12345.6789,-2) = 12,400
=ROUNDUP(12345.6789,-1) = 12,350
=ROUNDUP(12345.6789,0) = 12,346
=ROUNDUP(12345.6789,1) = 12,345.7
=ROUNDUP(12345.6789,2) = 12,345.68
=ROUNDUP(12345.6789,3) = 12,345.679

So, to answer your question: if your value is in A1, use =ROUNDUP(A1,-1)