Introduction to Excel Floor and Excel Ceiling Function

Excel function tutorials

Back in November, I had written a post about the excel round function and had also discussed roundup and rounddown, which you can easily figure out how to use. Sometimes, those 3 are not enough though. I received the following question which I thought was very interesting:

“How can I round to a more precise number? For example, Instead of rounding to the nearest unit or decimal, I would like to round to the nearest 0.05.”

Here is an example of numbers that could be used:

Then, you could use one of the two following functions:

Floor function is the equivalent of rounddown but will round down to that same precision. I personally use it a lot when I am averaging prices to the nearest nickel. For example, Canada is getting rid of its penny. All prices will be rounded to the nearest nickel. Those prices could be either rounded up or down. Here is the result by rounding down with the floor function. I am using the following:

=FLOOR(C3,0.05)

The result is:

Then, I can do the ceiling to round up to the higher nickel as follows:

=CEILING(B3,0.05)

As always, you can download the spreadsheet here!

***************************************************

Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss


***************************************************