FM Function of the Week: Floor and Ceiling

A few weeks ago we looked at the Min and Max functions. That got me wondering if there are any other FileMaker functions that travel in pairs. I found a few, but the most apt to follow Min and Max have to be Floor and Ceiling. Where Min and Max examine a range of values to determine the lowest or highest values, Floor and Ceiling each take a single number and return the closest lower or higher integer.

Essentially Floor and Ceiling are specialized types of rounding. The rounding we all learned in elementary school tells us to choose the closest whole  number when starting with a decimal value. For example 5.2 rounds down to 5 and 7.6 rounds up to 8. With Floor, any decimal value at all will result in rounding down. Ceiling rounds everything up.

Floor ( 99.99999 ) = 99

Ceiling ( 1.000001 ) = 2

About as straight forward as a function can get.

Extra Credit
The crafty and clever among you may have spotted some apparent duplication of effort on FileMaker’s part. Floor lops off everything to the right of the decimal point, right? If that sounds familiar it’s because the Int (as in integer) function does exactly the same thing. So why have two functions that do the same thing? Well they’re not, in fact, identical. To see why you’re going to have to stop thinking positive and start thinking negative.

For positive numbers Floor and Int do indeed return the same results. For negative numbers, they do not. Int ( -3.1 ) returns -3 but Floor ( -3.1 ) returns -4.

This entry was posted in FileMaker 13, Function of the Week. Bookmark the permalink.