SOLVED

US treasury price conversion eg 123'18.5 to 123.536

Occasional Contributor

US treasury price conversion eg 123'18.5 to 123.536

Hi Friends

I look forward to your email / help

Thanks

8 Replies

Re: US treasury price conversion eg 123'18.5 to 123.536

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)

Re: US treasury price conversion eg 123'18.5 to 123.536

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

Re: US treasury price conversion eg 123'18.5 to 123.536

If that is the intention, it is better indeed. Thanks!

Re: US treasury price conversion eg 123'18.5 to 123.536

Also not correct. If without rounding it returns 123.5645, but it shall be

Price conversion between fractional and decimal notation (iotafinance.com)

Re: US treasury price conversion eg 123'18.5 to 123.536

0.5625 corresponds to 18/32, so it ignores the .5 at the end...

Re: US treasury price conversion eg 123'18.5 to 123.536

You are right. Strange, why do we need it if so?

Re: US treasury price conversion eg 123'18.5 to 123.536

I think the OP will have to tell us what they really need.

best response confirmed by HHDXB (Occasional Contributor)
Solution

Re: US treasury price conversion eg 123'18.5 to 123.536

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