Floor and Ceiling Functions

While the ROUNDUP (round up to the nearest number, specifying the number of digits), ROUNDDOWN (round down to the nearest number, specifying the number of digits), and ROUND (round to nearest number up or down, specifying the number of digits) are very helpful, sometimes you wish to round up or down to a specific multiple.  Maybe you are dealing with large numbers and want to deal work only with multiples of 1,000 or 10,000.  Or maybe you’re building a graph or chart and want to minimize the scale.  The best way to do that, you decide, is to make the base (lower bound value) lower than, but as close to, the lowest number as possible.

No matter the reason, performing this is easy.  For each of them, you only need the number you are rounding and the multiple you’d like to round (up or down) to.  Examples follow:

Value: A1=1,798

Floor(A1,5)=1,795
Floor(A1,10)=1,790
Floor(A1,25)=1,775
Floor(A1,100)=1,700
Floor(A1,1,000)=1,000
Floor(A1,1,500)=1,500
Floor(A1,2,000)=0

Ceiling(A1,5)=1,800
Ceiling(A1,10)=1,800
Ceiling(A1,25)=1,800
Ceiling(A1,100)=1,800
Ceiling(A1,1,000)=2,000
Ceiling(A1,1,500)=3,000
Floor(A1,2,000)=0

As always, if you have questions, drop me a line or post a comment.

Advertisements

About bbluford
I am an executive finance professional with a love for process and application development (MS Access, Excel, Quickbooks), mostly as it relates to Accounting and Business Functions. I also love to write and share ideas with other people in this world. I'm an admitted Gym Rat who works out excessively. The best summation of me is that I love to teach and to learn.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: