![]() |
#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!! |
|
![]() |
||||
Thread | Thread Starter | Forum | Replies | Last Post |
Charting Help - Stock Prices | Ligerdub | PowerPoint | 0 | 01-03-2012 04:59 AM |