Jul 12 2021 04:10 AM
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)
Jul 12 2021 04:28 AM
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
Jul 12 2021 04:31 AM
If that is the intention, it is better indeed. Thanks!
Jul 12 2021 04:34 AM
Also not correct. If without rounding it returns 123.5645, but it shall be
Price conversion between fractional and decimal notation (iotafinance.com)
Jul 12 2021 04:42 AM
0.5625 corresponds to 18/32, so it ignores the .5 at the end...
Jul 12 2021 04:57 AM
You are right. Strange, why do we need it if so?
Too complex for me Microsoft Word - Treasury Futures Price Rounding Conventions _March 2010.docx (cmegroup.com)
Jul 12 2021 05:01 AM
I think the OP will have to tell us what they really need.
Jul 12 2021 06:24 AM
SolutionJul 12 2021 06:24 AM
Solution