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

Just a hint to go forward

Suppose A1 = 111-222

The below formula will give you the numbers before the hyphen no matter what
the length is
=VALUE(LEFT(A1,FIND("-",A1)-1))

And the below formula will give you the numbers after the hyphen to a
maximum of 10..which is more than enough

=VALUE(MID(A1,FIND("-",A1)+1,10))

If this post helps click Yes
---------------
Jacob Skaria


"pakeez" wrote:


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

Assuming your decimal value is in C1, this will convert it back to the
format you showed...

=INT(C1)&"-"&320*MOD(C1,1)

--
Rick (MVP - Excel)


"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

"Rick Rothstein" wrote:
=INT(C1)&"-"&320*MOD(C1,1)

Test with 110-160 minus 99-159. I believe the desired result is 1-001.
Ostensibly, your formula will result in 1-1. But it actually results in
11-0.999999999999091.


----- original message -----

"Rick Rothstein" wrote in message
...
Assuming your decimal value is in C1, this will convert it back to the
format you showed...

=INT(C1)&"-"&320*MOD(C1,1)

--
Rick (MVP - Excel)


"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

Good point. Your TEXT function call to limit the entry to 3 digits is the
way to go. Thanks for noting the problem with my formula.

--
Rick (MVP - Excel)


"JoeU2004" wrote in message
...
"Rick Rothstein" wrote:
=INT(C1)&"-"&320*MOD(C1,1)

Test with 110-160 minus 99-159. I believe the desired result is 1-001.
Ostensibly, your formula will result in 1-1. But it actually results in
11-0.999999999999091.


----- original message -----

"Rick Rothstein" wrote in message
...
Assuming your decimal value is in C1, this will convert it back to the
format you showed...

=INT(C1)&"-"&320*MOD(C1,1)

--
Rick (MVP - Excel)


"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

"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

Errata....

I wrote:
If C1 is =A1-B1, then:
int(C1)&"-"&text(mod(C1,1)*320,"000")

I meant that A1 and B1 are the decimal equivalents. I probably should have
use A2, B2 and C2 in my example to avoid confusion.


----- original message -----

"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

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

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

"Rick Rothstein" wrote:
=--(SUBSTITUTE(A1,"-"," ")&"/320")

Nifty!


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"))

I had a similar thought after seeing your first follow-up. But for
defensive-programming purposes, I would suggest:

=--(SUBSTITUTE(A1,"-"," ")&"/32"&IF(MID(A1,MAX(1,LEN(A1)-3),1)<"-","","0"))

That allows for the right part to be 1 digit as well as 2 or 3. I don't
know if that's necessary; I'm not familiar with the format of T-bond prices,
other than what Pakeez wrote. But the flexibility comes with no cost, even
if it is unnecessary.

(Well, almost "no cost". I put MAX(1,...) in at the last minute to handle
cases like 1-1.)

Fun stuff! :-)


----- original message -----

"Rick Rothstein" wrote in message
...
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

See inline comments...

=--(SUBSTITUTE(A1,"-"," ")&"/320")

Nifty!

Thanks


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"))

I had a similar thought after seeing your first follow-up. But for
defensive-programming purposes, I would suggest:

=--(SUBSTITUTE(A1,"-","
")&"/32"&IF(MID(A1,MAX(1,LEN(A1)-3),1)<"-","","0"))

That allows for the right part to be 1 digit as well as 2 or 3. I don't
know if that's necessary; I'm not familiar with the format of T-bond
prices, other than what Pakeez wrote. But the flexibility comes with no
cost, even if it is unnecessary.

(Well, almost "no cost". I put MAX(1,...) in at the last minute to handle
cases like 1-1.)

I think this would work as well (and it avoids the need for the MAX function
call)...

=--(SUBSTITUTE(A1,"-"," ")&"/32"&IF(LEN(A1)-FIND("-",A1)=3,"0",""))


Fun stuff! :-)

Yep! It sure is. ;-)


--
Rick (MVP - Excel)