Rounding is fun

Example

Formula

Round a number to an integer

Pi() = 3.14159265... -> 3

=ROUND(PI(),0)

Round a number to two digits

Pi() = 3.14159265... -> 3.14

=ROUND(PI(),2)

Round a number to next thousand

2009 -> 2000

=ROUND(2009,-3)

Round a number to 50p

5.67 -> 5.5

=ROUND(5.67*2,0)/2
is better than
=ROUND(5.67/5,1)*5
because you should prefer powers of 2 (floating numbers are being
represented without rounding errors)

Round down if 35p or less, else round up

2.36 -> 3

=CEILING(2.36-0.35,1)

Round to 1/64

2.1718 -> 2.171875

=ROUND(2.1718*64,0)/64

Show bond price in US
T-Note format (see Bloomberg © for example)

100.578125 -> 100-18.5

=INT(100.578125)&REPT("-"&MOD(100.578125,1)*32,SIGN(MOD(
100.578125,1)))

Show bond price in US
T-Note format (see Bloomberg © for example)
2^nd version

100.578125 -> 100-18½

=INT(100.578125)&REPT("-"&INT(MOD(100.578125,1)*32)&REPT
(CHAR(187+INT((MOD(100.578125,1)-INT(MOD(100.578125,1)*32
)/32)*128)),INT((MOD(100.578125,1)-INT(MOD(100.578125,1)*32
)/32)*128)>0),INT(MOD(100.578125,1)*128)>0)
Please note that this formula shows the highest tradeable price
which is lower or equal to the input

Round a date time to an hour

26/04/2010 16:37:12 ->
26/04/2010 17:00:00

=ROUND("26/04/2010  16:37:12"*24,0)/24

Round a date time to next 10 min point

26/04/2010 16:37:12 ->
26/04/2010 16:40:00

=ROUND("26/04/2010  16:37:12"*24*6,0)/24/6

Round to 1/8 but show 1/4,1/2,3/4 instead of 2/8,4/8,6/8

-2.5 -> -2 1/2

=CHOOSE(2+SIGN(-2.5),"-","0","") & IF(INT(ABS(-2.5))<>0,INT(ABS(-2.5))&" ","") & CHOOSE(1+ROUND((ABS(-2.5)-INT(ABS(-2.5)))*8,0),"", "1/8","1/4","3/8","1/2","5/8","3/4","7/8")