Prices from decimals to ticks

[rollie]

I'm looking for a formula to change bond prices from decimals to ticks. A tick is 1/32 of a point, so 99.50 would be 99-16. Additionally, after the number of ticks is listed, the following number should be in 1/8s of 1/32. So 99.5078 would be 99-162 (99 and 16/32s and 2/8's of a 32nd.)



I currently have the following formula, but it does not return the correct value in every situation.
=SUBSTITUTE(TRUNC(DOLLARFR(B1,32),2),".","-")&HLOOKUP(((B1-INT(B1))*32)-INT((B1-INT(B1))*32),{0,0.125,0.25,0.375,0.5,0.625,0.75,0. 875,1;"","1","2","3","+","5","6","7",""},2,TRUE)

i.e. for 109.0078 this returns 1092 when it should be 109-002....and for 108.9375 it returns 108-3 when it should be 108-30.

any help is greatly appreciated!!

[macropod]

Hi Rollie,

Try:
=INT(B1)&"-"&TEXT(INT(MOD(B1,1)*32),"00")&TEXT(ROUND(MOD(B1*3 2,1)*8,0),"0")

If you want to suppress the final '0' (eg 180-30 instead of 108-300), try:
=INT(B1)&"-"&TEXT(INT(MOD(B1,1)*32),"00")&TEXT(ROUND(MOD(B1*3 2,1)*8,0),"#")