How to roundup a number to the closest ten

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

You can use the function MROUND  lt reference cell gt    lt round to multiple of digit needed gt     Example    For a value A1   21 round to multiple of 10 it would be written as  MROUND A1 10  for which Result   20 For a value Z4   55 1 round to multiple of 10 it would be written as  MROUND Z4 10  for which Result   60

[excel] How to roundup a number to the closest ten?

Examples related to excel

Examples related to excel-formula

Examples related to vba