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