Sulprobil

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

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"*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")

Last modified 2yr ago

Copy link