Rounding Fun

Search…
Rounding Fun
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
2020 -> 2000
=ROUND(2020,-3)
Round a number to 50p
5.67 -> 5.5
=ROUND(5.672,0)/2 is better than =ROUND(5.67/5,1)*5 because you should prefer powers of 2 (floating numbers are 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
Please note if you like to represent a float by a close rational number then look at sbNRN​
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) 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"246,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")
Overlapping Ranges
Same Weekday and Calendarweek last year
Last modified 2yr ago
Copy link