![]() |
#1
|
|||
|
|||
![]()
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!! |
#2
|
||||
|
||||
![]()
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),"#")
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
![]() |
|
![]() |
||||
Thread | Thread Starter | Forum | Replies | Last Post |
Charting Help - Stock Prices | Ligerdub | PowerPoint | 0 | 01-03-2012 04:59 AM |