Conversion of numbers
 

Hi! I have a list with prices of US Treasury bonds. They are quoted in a
partcular way that I would like to convert to decimal form. They are quoted
like this eg: 99-02. Now this means that the price is 99 and some decimal.
The two first numbers are the number of 32:s. Thus in this case the price is
99 and 2/32. However if there are three decimals eg 99-021 the last digit
refers to the number of 124:s. Thus the price is 99 and 2/32 and 1/256.
Sometimes There are only two decimals followed by a plus or minus sign eg
99-30+ or 99-10-. The plus/minus indicates that you should add/subtract 1/64.
Is there any way that I can convert these price quotes into decimal form
using worksheet functions? Any help very much appreciated! Thanks a lot in
advance!

Conversion of numbers
 

1) I will assume there are always 2 digits before the first hyphen
2) You state:"However if there are three decimals eg 99-021 the last digit
refers to the number of 124:s. Thus the price is 99 and 2/32 and 1/256." I
will assume this should be 1/124
With the coded value (and Oh what a code!) in A1
In B1 we get the dollar amount with =--LEFT(A1,2)
In C1 we get the 32-th bit with =MID(A1,4,2)/32
In D1 we get the 1/124 bit or the 1/64 correction with
=IF(ISNUMBER(--MID(A1,6,1)),RIGHT(A1)/124,IF(RIGHT(A1)="+",1/64,IF(RIGHT(A1)="-",-1/64,0)))
And in E1 we get the total with =SUM(B1:D1)
I recommend you test this with more examples
I will leave it to use to combine all of this into one simple formula if so
desired
And I expect others to give a us nice VBA solution
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Arne Hegefors" wrote in message
...
Hi! I have a list with prices of US Treasury bonds. They are quoted in a
partcular way that I would like to convert to decimal form. They are
quoted
like this eg: 99-02. Now this means that the price is 99 and some decimal.
The two first numbers are the number of 32:s. Thus in this case the price
is
99 and 2/32. However if there are three decimals eg 99-021 the last digit
refers to the number of 124:s. Thus the price is 99 and 2/32 and 1/256.
Sometimes There are only two decimals followed by a plus or minus sign eg
99-30+ or 99-10-. The plus/minus indicates that you should add/subtract
1/64.
Is there any way that I can convert these price quotes into decimal form
using worksheet functions? Any help very much appreciated! Thanks a lot in
advance!

Conversion of numbers
 

What a lovely typo
I will leave it to YOU ......
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Bernard Liengme" wrote in message
...
1) I will assume there are always 2 digits before the first hyphen
2) You state:"However if there are three decimals eg 99-021 the last digit
refers to the number of 124:s. Thus the price is 99 and 2/32 and 1/256." I
will assume this should be 1/124
With the coded value (and Oh what a code!) in A1
In B1 we get the dollar amount with =--LEFT(A1,2)
In C1 we get the 32-th bit with =MID(A1,4,2)/32
In D1 we get the 1/124 bit or the 1/64 correction with
=IF(ISNUMBER(--MID(A1,6,1)),RIGHT(A1)/124,IF(RIGHT(A1)="+",1/64,IF(RIGHT(A1)="-",-1/64,0)))
And in E1 we get the total with =SUM(B1:D1)
I recommend you test this with more examples
I will leave it to use to combine all of this into one simple formula if
so desired
And I expect others to give a us nice VBA solution
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Arne Hegefors" wrote in message
...
Hi! I have a list with prices of US Treasury bonds. They are quoted in a
partcular way that I would like to convert to decimal form. They are
quoted
like this eg: 99-02. Now this means that the price is 99 and some
decimal.
The two first numbers are the number of 32:s. Thus in this case the price
is
99 and 2/32. However if there are three decimals eg 99-021 the last digit
refers to the number of 124:s. Thus the price is 99 and 2/32 and 1/256.
Sometimes There are only two decimals followed by a plus or minus sign eg
99-30+ or 99-10-. The plus/minus indicates that you should add/subtract
1/64.
Is there any way that I can convert these price quotes into decimal form
using worksheet functions? Any help very much appreciated! Thanks a lot
in
advance!

Conversion of numbers
 

Unless you want to create a User Defined Function....
Try something like this:

For a quoted price in A1

This formula converts the quote to a decimal value
B1:
=LEFT(A1,FIND("-",A1)-1)+MID(A1,FIND("-",A1)+1,2)/32+CHOOSE(MAX(COUNTIF(A1,{"*-???","*-??+","*-??-","*-???+","*-???-"})*{1,2,3,4,5})+1,0,RIGHT(A1,1)/124,1/64,-1/64,LEFT(RIGHT(A1,2),1)/124+1/64,LEFT(RIGHT(A1,2),1)/124-1/64)

(yeah....I know....it's not very pretty)

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Arne Hegefors" wrote:

Hi! I have a list with prices of US Treasury bonds. They are quoted in a
partcular way that I would like to convert to decimal form. They are quoted
like this eg: 99-02. Now this means that the price is 99 and some decimal.
The two first numbers are the number of 32:s. Thus in this case the price is
99 and 2/32. However if there are three decimals eg 99-021 the last digit
refers to the number of 124:s. Thus the price is 99 and 2/32 and 1/256.
Sometimes There are only two decimals followed by a plus or minus sign eg
99-30+ or 99-10-. The plus/minus indicates that you should add/subtract 1/64.
Is there any way that I can convert these price quotes into decimal form
using worksheet functions? Any help very much appreciated! Thanks a lot in
advance!

Conversion of numbers
 

Thanks very much bernard. However the dollar amount (ie the non decimal
numbers) might be of one, two or three digits. Also the third decimal should
be 1/256. Please see top of page page 10:

http://www.newyorkfed.org/cfcbsweb/T...d_agencies.pdf

Thanks again Bernard!

"Bernard Liengme" skrev:

1) I will assume there are always 2 digits before the first hyphen
2) You state:"However if there are three decimals eg 99-021 the last digit
refers to the number of 124:s. Thus the price is 99 and 2/32 and 1/256." I
will assume this should be 1/124
With the coded value (and Oh what a code!) in A1
In B1 we get the dollar amount with =--LEFT(A1,2)
In C1 we get the 32-th bit with =MID(A1,4,2)/32
In D1 we get the 1/124 bit or the 1/64 correction with
=IF(ISNUMBER(--MID(A1,6,1)),RIGHT(A1)/124,IF(RIGHT(A1)="+",1/64,IF(RIGHT(A1)="-",-1/64,0)))
And in E1 we get the total with =SUM(B1:D1)
I recommend you test this with more examples
I will leave it to use to combine all of this into one simple formula if so
desired
And I expect others to give a us nice VBA solution
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Arne Hegefors" wrote in message
...
Hi! I have a list with prices of US Treasury bonds. They are quoted in a
partcular way that I would like to convert to decimal form. They are
quoted
like this eg: 99-02. Now this means that the price is 99 and some decimal.
The two first numbers are the number of 32:s. Thus in this case the price
is
99 and 2/32. However if there are three decimals eg 99-021 the last digit
refers to the number of 124:s. Thus the price is 99 and 2/32 and 1/256.
Sometimes There are only two decimals followed by a plus or minus sign eg
99-30+ or 99-10-. The plus/minus indicates that you should add/subtract
1/64.
Is there any way that I can convert these price quotes into decimal form
using worksheet functions? Any help very much appreciated! Thanks a lot in
advance!

Conversion of numbers
 

Arne,

For a string in cell A2:

=VALUE(LEFT(A2,FIND("-",A2)-1))+VALUE(MID(A2,FIND("-",A2)+1,2))/32+IF((LEN(A2)-FIND("-",A2))=3,IF(MID(A2,FIND("-",A2)+3,1)="-",-1/64,IF(MID(A2,FIND("-",A2)+3,1)
= "+", 1/64,MID(A2,FIND("-",A2)+3,1)/256)))

I just wasn't sure how to interpret this:

"refers to the number of 124:s. Thus the price is 99 and 2/32 and 1/256."

Should it actually be divided by 124, or by 256 - I used 256 in the formula.

HTH,
Bernie
MS Excel MVP


"Arne Hegefors" wrote in message
...
Hi! I have a list with prices of US Treasury bonds. They are quoted in a
partcular way that I would like to convert to decimal form. They are quoted
like this eg: 99-02. Now this means that the price is 99 and some decimal.
The two first numbers are the number of 32:s. Thus in this case the price is
99 and 2/32. However if there are three decimals eg 99-021 the last digit
refers to the number of 124:s. Thus the price is 99 and 2/32 and 1/256.
Sometimes There are only two decimals followed by a plus or minus sign eg
99-30+ or 99-10-. The plus/minus indicates that you should add/subtract 1/64.
Is there any way that I can convert these price quotes into decimal form
using worksheet functions? Any help very much appreciated! Thanks a lot in
advance!

Conversion of numbers
 

Per your reference to the treasury PDF file....

Try this:

A1: (a quoted price)

The decimal value would be
B1:
=LEFT(A1,FIND("-",A1)-1)+MID(A1,FIND("-",A1)+1,2)/32+CHOOSE(MAX(COUNTIF(A1,{"*-???","*-??+","*-??-","*-???+","*-???-"})*{1,2,3,4,5})+1,0,RIGHT(A1,1)/256,1/64,-1/64,LEFT(RIGHT(A1,2),1)/256+1/64,LEFT(RIGHT(A1,2),1)/256-1/64)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

Unless you want to create a User Defined Function....
Try something like this:

For a quoted price in A1

This formula converts the quote to a decimal value
B1:
=LEFT(A1,FIND("-",A1)-1)+MID(A1,FIND("-",A1)+1,2)/32+CHOOSE(MAX(COUNTIF(A1,{"*-???","*-??+","*-??-","*-???+","*-???-"})*{1,2,3,4,5})+1,0,RIGHT(A1,1)/124,1/64,-1/64,LEFT(RIGHT(A1,2),1)/124+1/64,LEFT(RIGHT(A1,2),1)/124-1/64)

(yeah....I know....it's not very pretty)

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Arne Hegefors" wrote:

Hi! I have a list with prices of US Treasury bonds. They are quoted in a
partcular way that I would like to convert to decimal form. They are quoted
like this eg: 99-02. Now this means that the price is 99 and some decimal.
The two first numbers are the number of 32:s. Thus in this case the price is
99 and 2/32. However if there are three decimals eg 99-021 the last digit
refers to the number of 124:s. Thus the price is 99 and 2/32 and 1/256.
Sometimes There are only two decimals followed by a plus or minus sign eg
99-30+ or 99-10-. The plus/minus indicates that you should add/subtract 1/64.
Is there any way that I can convert these price quotes into decimal form
using worksheet functions? Any help very much appreciated! Thanks a lot in
advance!

Conversion of numbers
 

Ok...here it is....the best I could come up with:

B1:
=LEFT(A1,FIND("-",A1)-1)+SUM(LOOKUP(MID(A1,FIND("-",A1)+{1,2,3,4},1),{"","-","+","0","1","2","3","4","5","6","7","8","9"} ,{0,-4,4,0,1,2,3,4,5,6,7,8,9})/{3.2,32,256,256})

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

Per your reference to the treasury PDF file....

Try this:

A1: (a quoted price)

The decimal value would be
B1:
=LEFT(A1,FIND("-",A1)-1)+MID(A1,FIND("-",A1)+1,2)/32+CHOOSE(MAX(COUNTIF(A1,{"*-???","*-??+","*-??-","*-???+","*-???-"})*{1,2,3,4,5})+1,0,RIGHT(A1,1)/256,1/64,-1/64,LEFT(RIGHT(A1,2),1)/256+1/64,LEFT(RIGHT(A1,2),1)/256-1/64)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

Unless you want to create a User Defined Function....
Try something like this:

For a quoted price in A1

This formula converts the quote to a decimal value
B1:
=LEFT(A1,FIND("-",A1)-1)+MID(A1,FIND("-",A1)+1,2)/32+CHOOSE(MAX(COUNTIF(A1,{"*-???","*-??+","*-??-","*-???+","*-???-"})*{1,2,3,4,5})+1,0,RIGHT(A1,1)/124,1/64,-1/64,LEFT(RIGHT(A1,2),1)/124+1/64,LEFT(RIGHT(A1,2),1)/124-1/64)

(yeah....I know....it's not very pretty)

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Arne Hegefors" wrote:

Hi! I have a list with prices of US Treasury bonds. They are quoted in a
partcular way that I would like to convert to decimal form. They are quoted
like this eg: 99-02. Now this means that the price is 99 and some decimal.
The two first numbers are the number of 32:s. Thus in this case the price is
99 and 2/32. However if there are three decimals eg 99-021 the last digit
refers to the number of 124:s. Thus the price is 99 and 2/32 and 1/256.
Sometimes There are only two decimals followed by a plus or minus sign eg
99-30+ or 99-10-. The plus/minus indicates that you should add/subtract 1/64.
Is there any way that I can convert these price quotes into decimal form
using worksheet functions? Any help very much appreciated! Thanks a lot in
advance!

Conversion of numbers
 

Arne Hegefors wrote...
Hi! I have a list with prices of US Treasury bonds. They are quoted in a
partcular way that I would like to convert to decimal form. They are quoted
like this eg: 99-02. Now this means that the price is 99 and some decimal.
The two first numbers are the number of 32:s. Thus in this case the price is
99 and 2/32. However if there are three decimals eg 99-021 the last digit
refers to the number of 124:s. Thus the price is 99 and 2/32 and 1/256.
Sometimes There are only two decimals followed by a plus or minus sign eg
99-30+ or 99-10-. The plus/minus indicates that you should add/subtract 1/64.
....

Yet another alternative.

=LEFT(x,FIND("-",x)-1)+MID(x,FIND("-",x)+1,2)/32+IF(COUNTIF(x,"*-???"),
CHOOSE(FIND(RIGHT(x,1),"+-"&RIGHT(x,1)),4,-4,RIGHT(x,1))/256)