I am a supporter ofSt. Joseph's hospice. If you find this site useful or if it helped you, consider a small donation toSt. Joseph's, please. Information onSt. Joseph's
 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)/2is better than=ROUND(5.67/5,1)*5because 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 UST-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 UST-Note format (see Bloomberg © for example)2nd 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")