Conversions
Posted by Dee on October 22, 2001 9:00 AM
TBond futures are quoted in 32nds. If I input 90.12, that means 90*32+12 which is 2792. Can you supply me with a formula for changing 90.12 to 2792 & then back again to 90.12? I'm switching from Lotus 1-2-3 & I have a formula for that in 1-2-3 but Excel doesn't recognize nor convert it correctly. Thank you in advance.
Posted by Barrie Davidson on October 22, 2001 10:46 AM
Your formula (90*32+12) equals 2892. Assuming your 2792 is just a typo, try this formula which equates to 2892 (assumes 90.12 in cell A1).
=INT(A1)*32+12
Regards,
BarrieBarrie Davidson
Posted by Dee on October 22, 2001 10:58 AM
Thank you - I already know that formula - but I don't want to have to input anything into the formula - you've insinuated that I have to add the number 12 to the formula.
Posted by IML on October 22, 2001 11:21 AM
You could use
=INT(A1)*32+RIGHT(A1,LEN(A1)-FIND(".",A1))
to convert. I'd just refer back to A1 to "unconvert". Or if you need a formula, assuming you converted in B1, use
=INT(B1/32)+MOD(B1,32)/100
Posted by Dee on October 22, 2001 12:00 PM
Again, thank you. Now if I have a number such as 2898, how do I convert that to another number such as XX.X (similar 90.12 but now 6 higher)?
Posted by Barrie Davidson on October 22, 2001 12:23 PM
Dee, use IML's formula
=INT(B1/32)+MOD(B1,32)/100
that will convert 2898 to 90.18.
Is this what you need?
BarrieBarrie Davidson
Posted by dee on October 22, 2001 12:38 PM
Again & again, thank all of you for your prompt, knowledgable & informative answers to my questions. As a former user of Lotus 123 & now a new user of Excel I'm glad I found this site.
Posted by IML on October 22, 2001 12:43 PM
This needs fixing
beware of that formula, it has troubles coverting back 90.1 vs 90.01 etc. I'm unable to get a workable answer...
Posted by IML on October 22, 2001 1:08 PM
Maybe
To get A1 to xxxx for
=INT(A1)*32+(RIGHT(A1,LEN(A1)-FIND(".",A1)+1)*100)
To get c1 to xxx.xx form
=(INT(C1/32)&"."&TEXT((C1/32-INT(C1/32))*32,"00"))+0
my head hurts.
Posted by dee on October 23, 2001 6:52 AM
Re: Maybe
sorry about your headache! so does mine!
Posted by Aladin Akyurek on October 23, 2001 6:55 AM
Re: Maybe
Dee,
Just curious: Would you post the formulas back and fro you used in Lotus?
Aladin
Posted by Dee on October 24, 2001 6:57 AM
Re: Maybe
@INT(A1/32)+@MOD(A1,32)/100
@INT(B1)*32+((B1)-@INT(B1))*100
Similar to your formulas. So far the conversions you supplied seems to be working. Again, THANK YOU
Posted by Aladin Akyurek on October 24, 2001 8:04 AM
Credit...
must go to Ian/IML, Dee. I was just watching his headache & became curious about your Lotus equivalents.
By the way, why did you switch? Motives emanating from Lotus or something else?
Regards,
Aladin
=========
Posted by Dee on October 24, 2001 11:28 AM
Re: Credit...
I switched because my Lotus 1-2-3 version was so old (I don't want to spend the money to upgrade) & also because I just purchased a new comouter with Excel preinstalled. I must admit, however my 1-2-3 program is much, much more user friendly. I find that even the basics in Excel are very confusing & the help buttons are usually of no help. I probably will be asking this site some very basic questions soon.
