I am writing a estimating table and want to have an option to round "up" the total to the nearest dollar, ten dollars, hundred dollars etc. The problem is the round function rounds up or down depending on the cutoff, not always up. I know about the "ceiling" and "floor" functions but need to round 'Up' past the decimal point.I don't want the total to be $12,400 where the round function will make it $12,000. I want to go up to $13,000.

Has anyone solved this before? Thanks in advance.

Do you mean you want to round up to the nearest thousand?

Well, I wrote different scripts to do different things. Example: round to nearest tens, hundreds, etc.

The problem is I need it to round up everytime.

Ah, okay - you want a generic one. Try:

Ceiling ( number * 10^precision ) * 10^-precision

This works similar to the Round() function, with two parameters.

Not doing what I need or I'm not getting it. Thanks

To round up to the nearest cent, use a precision of 2:

Ceiling ( 1111.111 * 10^2 ) * 10^-2

returns 1111.12.

To round up to the nearest hundred, use a precision of -2:

Ceiling ( 1111.111 * 10^-2 ) * 10^2

returns 1200.

To round up to the nearest thousand, use a precision of -3:

Ceiling ( 1111.111 * 10^-3 ) * 10^3

returns 2000. And so on.

Got it - thanks so much - works great!

Hi Michael,

Correct me if I am wrong here, but it doesnt seem consistant on both sides of the deciaml if the user puts in a precision that expands beyond the original number.

Perhaps this slight modification will make it more consistent?

Let ( [ e = Floor ( Log ( number ) ); 

        p = Case ( precision < -1 * e; -e; precision )

        ];

              SetPrecision ( Ceiling ( number * 10^p ) * 10^-p ; p )

       )

Can you give an example of the problem?

-4 will give you 10,000

+4 will give you 1111.111

Its not really a problem so much but I am not sure that it really should expand beyond the current precison of the highest placed digit.

-4 will give you 10,000

+4 will give you 1111.111

Aren't these results correct? Even 0.01 rounded up to the nearest 10,000 should return 10,000 IMHO.

Perhaps. Mathematically, you are of course correct, but I am not sure if that would translate well for end users. I was just seemed weird. lol.

Perhaps it seems weird to you because you are looking at the way the numbers are written, instead of their underlying values? I think of rounding as dividing the continuum of values into discrete ranges; anything within a range is converted to a boundary value.

Certainly, in some circumstances it makes sense to round to the nearest order of magnitude - but then there should be no need for a precision parameter, I think.