However, initially you said "110-16 which is equivalent to 110 16/32".
Did you really mean 110-160, as you use later? That is, is the right
part always 3 digits?
I hope so. It would be difficult, but not insurmountable, to
differentiate 2-digit and 3-digit right parts.
And this variation will distinguish between 2 and 3 digit values after the
dash...
=--(SUBSTITUTE(A1,"-"," ")&"/32"&IF(MID(A1,LEN(A1)-2,1)="-","","0"))
so that 110-16 and 110-160 will evaluate to the same value.
--
Rick (MVP - Excel)
"Rick Rothstein" wrote in message
...
Yes. But according to your example, the left part is not always 3
digits. Ostbensibly, the more versatile conversion to decimal is:
left(A1,find("-",A1)-1)+right(A1,3)/320
Just been thinking about this; perhaps this, with only one function call,
would be the better way to calculate the decimal value of the OP's
formatted numbers...
=--(SUBSTITUTE(A1,"-"," ")&"/320")
--
Rick (MVP - Excel)
"JoeU2004" wrote in message
...
"pakeez" wrote:
110-16 is in cell A1 then formula for converting to
decimal would be: =Left(A1,3)+Right(A1,3)/320
Yes. But according to your example, the left part is not always 3
digits. Ostbensibly, the more versatile conversion to decimal is:
left(A1,find("-",A1)-1)+right(A1,3)/320
However, initially you said "110-16 which is equivalent to 110 16/32".
Did you really mean 110-160, as you use later? That is, is the right
part always 3 digits?
I hope so. It would be difficult, but not insurmountable, to
differentiate 2-digit and 3-digit right parts.
Since I have been out of touch with excel for long
time I am having hard time how to format back in xxx-xx.
If C1 is =A1-B1, then:
int(C1)&"-"&text(mod(C1,1)*320,"000")
----- original message ----
"pakeez" wrote in message
...
As you may know that T-Bonds price format is xxx-xxx (e.g 110-16 which
is equivalent to 110 16/32 or 99-245 which is equivalent to 99
24.5/32)
Now I need to do some calaculaltions while trading Bonds and I want to
do in excel 03.
I was given a hint that I need to convert the above price format first
in decimal value and then format the result in xxx-xxx.
e.g 110-16 is in cell A1 then formula for converting to decimal would
be:
=Left(A1,3)+Right(A1,3)/320
Since I have been out of touch with excel for long time I am having
hard time how to format back in xxx-xxx.
So I am giving an example below to do calculations to do price
difference:
say cell A1 has value of 110-160
say cell B1 has value of 99-245
The answer is 10-235.
Now how can I get this calculations done in excel. Detail formulas
would be appreciated.
Thank YOu
--
pakeez
------------------------------------------------------------------------
pakeez's Profile:
http://www.thecodecage.com/forumz/member.php?userid=278
View this thread:
http://www.thecodecage.com/forumz/sh...ad.php?t=92522