MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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.