Forum Discussion
Solved
US treasury price conversion eg 123'18.5 to 123.536
Hi Friends I look forward to your email / help Thanks
- Thank you all for your time and input.
Hans Vogelaar : your setout with reverse calculation was a winner ..... I tested all 100% ..... excellent
Sergei Baklan : your set out to part of the question ... again worked well ... thank you
With 133'18.5 in F3, the formula to convert it to a regular number is
=LEFT(F3,FIND("'",F3)-1)+MID(F3,FIND("'",F3)+1,10)/32
With 133.56 in L3, the formula to convert it to US Treasury notation is
=INT(L3)&"'"&MROUND(MOD(L3,1)*32,0.5)
That returns 133.58. I'm not familiar with fractional notation, my guess for 123'18.5 123 is taken as it is, next two is fraction of 32 and the rest is fraction of 256, i.e.
=ROUND(LEFT(A1,SEARCH("'",A1)-1) + MID(A1,SEARCH("'",A1)+1,2)/32+RIGHT(A1, LEN(A1)-SEARCH("'",A1)-2)/256,2)which returns 123.56
If that is the intention, it is better indeed. Thanks!