Rounding to a given number

These custom functions round a number “naturally”. For example, they can be used to round time to 15 minutes, or money to $.25, or just any number to a multiple of another number.

All the functions have the same syntax:

Round To( number, precision )

Round Down To( number, precision )

Round Up To( number, precision )

where number is the number to round and precision is the number to calculate the appropriate multiple of. For example, Round To( 13, 5 ) rounds 13 to the nearest multiple of 5, i.e. 15. The Round Up To() and Round Down To() round the number to higher or lower multiple of precision respectively.

Functions themselves are very simple:

Round To( number, precision )

Round( number / precision; 0 ) * precision

Round Down To( number, precision )

Floor( number / precision; 0 ) * precision

Round Up To( number, precision )

Ceiling( number / precision; 0 ) * precision

How to round time

Don't calculate the number of seconds: use the Time() function instead. For example, to round to an hour:

Round To( Time Field, Time( 1, 0, 0 ) )

to 15 minutes:

Round To( Time Field, Time( 0, 15, 0 ) )

You might also want to make a few constant custom functions: HOURS and MINUTES. These function must return Time( 1, 0, 0 ), Time( 0, 1, 0 ) respectively. (You could make a function for seconds, if you need them, but you'll need to select a name carefully, because Seconds is taken already.) With such functions your code will be more readable, like this:

Round To( Time Field, 2 * HOURS )

Round To( Time Field, 0.5 * HOURS )

Round To( Time Field, 15 * MINUTES )

Round To( Time Field, 1/2 * MINUTES )

There's yet another good use of this function: it can help to approximate a number to a common fraction. I'll write about this later.

Test for Custom Function Tester

Assert Equals( Round To( 5; 1 ); 5 ) &
Assert Equals( Round To( 5; 2 ); 6 ) & 
Assert Equals( Round To( 5; 3 ); 6 ) & 
Assert Equals( Round To( 5; 4 ); 4 ) & 
Assert Equals( Round To( 5; 5 ); 5 ) &

Assert Equals( Round To( -5; 1 ); -5 ) &
Assert Equals( Round To( -5; 2 ); -6 ) &
Assert Equals( Round To( -5; 3 ); -6 ) &
Assert Equals( Round To( -5; 4 ); -4 ) &
Assert Equals( Round To( -5; 5 ); -5 ) &

Assert Equals( Round To( .5; .1 ); .5 ) &
Assert Equals( Round To( .5; .2 ); .6 ) &
Assert Equals( Round To( .5; .3 ); .6 ) &
Assert Equals( Round To( .5; .4 ); .4 ) &
Assert Equals( Round To( .5; .5 ); .5 ) &

Assert Equals( Round Up To( 5; 1 ); 5 ) &
Assert Equals( Round Up To( 5; 2 ); 6 ) & 
Assert Equals( Round Up To( 5; 3 ); 6 ) & 
Assert Equals( Round Up To( 5; 4 ); 8 ) & 
Assert Equals( Round Up To( 5; 5 ); 5 ) &

Assert Equals( Round Up To( -5; 1 ); -5 ) &
Assert Equals( Round Up To( -5; 2 ); -4 ) &
Assert Equals( Round Up To( -5; 3 ); -3 ) &
Assert Equals( Round Up To( -5; 4 ); -4 ) &
Assert Equals( Round Up To( -5; 5 ); -5 ) &

Assert Equals( Round Up To( .5; .1 ); .5 ) &
Assert Equals( Round Up To( .5; .2 ); .6 ) &
Assert Equals( Round Up To( .5; .3 ); .6 ) &
Assert Equals( Round Up To( .5; .4 ); .8 ) &
Assert Equals( Round Up To( .5; .5 ); .5 ) &

Assert Equals( Round Down To( 5; 1 ); 5 ) &
Assert Equals( Round Down To( 5; 2 ); 4 ) & 
Assert Equals( Round Down To( 5; 3 ); 3 ) & 
Assert Equals( Round Down To( 5; 4 ); 4 ) & 
Assert Equals( Round Down To( 5; 5 ); 5 ) &

Assert Equals( Round Down To( -5; 1 ); -5 ) &
Assert Equals( Round Down To( -5; 2 ); -6 ) &
Assert Equals( Round Down To( -5; 3 ); -6 ) &
Assert Equals( Round Down To( -5; 4 ); -8 ) &
Assert Equals( Round Down To( -5; 5 ); -5 ) &

Assert Equals( Round Down To( .5; .1 ); .5 ) &
Assert Equals( Round Down To( .5; .2 ); .4 ) &
Assert Equals( Round Down To( .5; .3 ); .3 ) &
Assert Equals( Round Down To( .5; .4 ); .4 ) &
Assert Equals( Round Down To( .5; .5 ); .5 ) &

Assert Equals( Round To( 5; 0 ); "?" ) &

Assert Equals( Round To( 0; 1 ); 0 ) &
Assert Equals( Round To( 0; 2 ); 0 ) &
Assert Equals( Round To( 0; 3 ); 0 ) &

Let( [ 
  t = Time( 1; 23; 45 );
  HOURS = Time( 1; 0; 0 );
  MINUTES = Time( 0; 1; 0 ) ];
  
  Assert Equals( Round To( t; 1/2 * MINUTES ); 
    Time( 1; 24; 00 ) ) &
  Assert Equals( Round To( t; MINUTES ); 
    Time( 1; 24; 0 ) ) &
  Assert Equals( Round To( t; 15 * MINUTES ); 
    Time( 1; 30; 0 ) ) &
  Assert Equals( Round To( t; HOURS ); 
    Time( 1; 0; 0 ) ) &
  Assert Equals( Round To( t; .5 * HOURS ); 
    Time( 1; 30; 0 ) ) )

(How to use this?)

Technorati tags: FileMaker, FileMaker 7, FileMaker 8.

Comments

[Paul]

This rounding solution was simple and easy to implement, we have used it for our time keeping system and it's saved us huge amounts of headache. Thanks!

[daniel bangham]

I am new to using blogs and forums. Please can you tell me how to get a copy of the round up to function described here?
I use filemaker 8 advanced.

[daniel bangham]

Ok. Very stupid of me, I now understand that you just use the formula given. Yes it works brilliantly, Thanks

[Janet Tokerud]

Hi Mikhail, just tried your round up function and noticed that with FileMaker Pro 9 the Ceiling function doesn't have a 2nd parameter. It is just Ceiling ( number ). I tried using that in your suggested calculation and it seems to work fine without the parameter.

Also, as you seem to be using TypePad, it would be great if you turn on the new TypePad Connect feature for your comments. I did it on my FileMaker Fever blog and it just takes 10 minutes or so to set up. Pretty much nothing. It converts all your comments to that new system. The reason for my request is I like it when I can keep track of my own comments later and see who has responded etc.. TypePad Connect makes it easier to do so.

Thanks for your great posts.

[m.edoshin]

Hi Janet,

You're right, I probably just mechanically altered the functions for the post. Floor() doesn't take an argument either.

I'd be happy to change the comment system, but there's no trace of this feature in the control panel. I read their help and it seems my blog must be moved to their new platform first, which is happening “as quickly as possible” :)

Take care.