Tags

, ,

A question that occurs from time to time is how to round a (monetary) amount to e.g. 5, 25, 50 cents. Basically this can be done easily using the ROUND function.

ROUNDING to 5 cents = ROUND(amount*20)/20
amount   multiplied by 20   rounded to integer   divided by 20
------   ----------------   ------------------   -------------
  1.02              20.40                   20            1.00 
  1.03              20.60                   21            1.05 
  1.37              27.40                   27            1.35

the same principle works for:

  •  round to 25 cents = ROUND(amount*4)/4
  •  round to 50 cents = ROUND(amount*2)/2

basically the general formula is:

ROUND(amount*(100/rounding_base)) / (100/rounding_base)