looking for formula to convert bond price to 32nds

columns have for example

79-10 which is 79 + 10/32
103-12 which is 103 + 12/32

79-10+ which is 79 + 10.5/32
103-12+ which is 103 + 12.5/32

saw a post on here about converting 32nds but not +'s

Bond Price Conversion Formula

To convert a bond price with a + to a decimal, use the formula:

For example, if you have a bond price of 79-10+, you would use the formula:

This would give you a decimal bond price of 79.328125.

To convert a bond price without a + to a decimal, use the same formula but with a whole number and a fraction that is not a half. For example, if you have a bond price of 103-12, you would use the formula:

This would give you a decimal bond price of 103.375.

1. Use the formula
   Formula:

   =Whole Number + (Fraction/32) 

   to convert bond prices to decimals.
2. For bond prices with a +, use the fraction as a decimal and add it to the whole number.
3. For bond prices without a +, use the fraction as a decimal and add it to the whole number.

Hi,

you don't need a formula just a format - select the cells and

Choose Format, Cells, Custom and enter the following on the Type line

# ??/32

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"novice" wrote:

looking for formula to convert bond price to 32nds

columns have for example

79-10 which is 79 + 10/32
103-12 which is 103 + 12/32

79-10+ which is 79 + 10.5/32
103-12+ which is 103 + 12.5/32

saw a post on here about converting 32nds but not +'s

There is no format which will display your examples as you want, because you
have text, not numbers.

If you have decimal numbers for your bonds (eg, 79.75) you can get it
displayed as 32nds with:

# ??/32

You will need an If formula to display what you want.

Regards,
Fred

"novice" wrote in message
...
looking for formula to convert bond price to 32nds

columns have for example

79-10 which is 79 + 10/32
103-12 which is 103 + 12/32

79-10+ which is 79 + 10.5/32
103-12+ which is 103 + 12.5/32

saw a post on here about converting 32nds but not +'s

Fred - you responded to this question with the following back in 2006. it
works for numbers that do not include a + which is 1/2 a 32nd

so 103-16 becomes 103.5

but 103-16+ does not work. this is a typical bond price quote and is = 103
& 16.5/32nds

I'm looking for a formula that captures this exception. any suggestions?


The formula would be quite simple:

=left(a1,2)+right(a1,2)/32

However, I suspect you also have prices over 100, so sometimes you would
have 3
characters to the left of the '-'. If so:

=left(a1,find("-",a1)-1)+right(a1,2)/32
"Fred Smith" wrote:

There is no format which will display your examples as you want, because you
have text, not numbers.

If you have decimal numbers for your bonds (eg, 79.75) you can get it
displayed as 32nds with:

# ??/32

You will need an If formula to display what you want.

Regards,
Fred

"novice" wrote in message
...
looking for formula to convert bond price to 32nds

columns have for example

79-10 which is 79 + 10/32
103-12 which is 103 + 12/32

79-10+ which is 79 + 10.5/32
103-12+ which is 103 + 12.5/32

saw a post on here about converting 32nds but not +'s

I should clarify that my ultimate goal is to display these prices in
decimals. I am receiving a download file that quotes in the format I
provided and need to convert to decimal to upload to another system.

Thanks

"Fred Smith" wrote:

There is no format which will display your examples as you want, because you
have text, not numbers.

If you have decimal numbers for your bonds (eg, 79.75) you can get it
displayed as 32nds with:

# ??/32

You will need an If formula to display what you want.

Regards,
Fred

"novice" wrote in message
...
looking for formula to convert bond price to 32nds

columns have for example

79-10 which is 79 + 10/32
103-12 which is 103 + 12/32

79-10+ which is 79 + 10.5/32
103-12+ which is 103 + 12.5/32

saw a post on here about converting 32nds but not +'s

I think this formula will do what you want...

=LEFT(A1,FIND("-",A1)-1)+MID(A1,FIND("-",A1)+1,2)/32+(RIGHT(A1)="+")/64

--
Rick (MVP - Excel)


"novice" wrote in message
...

I should clarify that my ultimate goal is to display these prices in
decimals. I am receiving a download file that quotes in the format I
provided and need to convert to decimal to upload to another system.

Thanks

"Fred Smith" wrote:

There is no format which will display your examples as you want, because
you
have text, not numbers.

If you have decimal numbers for your bonds (eg, 79.75) you can get it
displayed as 32nds with:

# ??/32

You will need an If formula to display what you want.

Regards,
Fred

"novice" wrote in message
...
looking for formula to convert bond price to 32nds

columns have for example

79-10 which is 79 + 10/32
103-12 which is 103 + 12/32

79-10+ which is 79 + 10.5/32
103-12+ which is 103 + 12.5/32

saw a post on here about converting 32nds but not +'s

That would work with an input like 103-09+ (with 2 digits for the number of
32nds), but if the input could be 103-9+ you might want

=IF(RIGHT(A1)="+",1/64+LEFT(A1,FIND("-",A1)-1)+MID(A1,FIND("-",A1)+1,LEN(A1)-FIND("-",A1)-1)/32,LEFT(A1,FIND("-",A1)-1)+MID(A1,FIND("-",A1)+1,LEN(A1)-FIND("-",A1))/32)--David Biddulph"Rick Rothstein" wrote in . ..I think this formula will do what you want... =LEFT(A1,FIND("-",A1)-1)+MID(A1,FIND("-",A1)+1,2)/32+(RIGHT(A1)="+")/64 -- Rick (MVP - Excel) "novice" wrote in ... I should clarify that my ultimate goal is to display these prices in decimals. I am receiving a download file that quotes in the format I provided and need to convert to decimal to upload to another system. Thanks "Fred Smith" wrote: There is no format which will display your examples as you want, becauseyou have text, not numbers. If you have decimal numbers for your bonds (eg, 79.75) you can get it displayed as 32nds with: # ??/32 You will need an If formula to display what you want. Regards, Fred "novice" wrote in message ... looking for formula to convert bond price to 32nds columns have for example 79-10 which is 79 + 10/32 103-12 which is 103 + 12/32 79-10+ which is 79 + 10.5/32 103-12+ which is 103 + 12.5/32 saw a post on here about converting 32nds but not +'s

Thank you! This works as well as the other & is fabulous. Saves me SO MUCH
TIME!

"David Biddulph" wrote:

That would work with an input like 103-09+ (with 2 digits for the number of
32nds), but if the input could be 103-9+ you might want

=IF(RIGHT(A1)="+",1/64+LEFT(A1,FIND("-",A1)-1)+MID(A1,FIND("-",A1)+1,LEN(A1)-FIND("-",A1)-1)/32,LEFT(A1,FIND("-",A1)-1)+MID(A1,FIND("-",A1)+1,LEN(A1)-FIND("-",A1))/32)--David Biddulph"Rick Rothstein" wrote in . ..I think this formula will do what you want... =LEFT(A1,FIND("-",A1)-1)+MID(A1,FIND("-",A1)+1,2)/32+(RIGHT(A1)="+")/64 -- Rick (MVP - Excel) "novice" wrote in ... I should clarify that my ultimate goal is to display these prices in decimals. I am receiving a download file that quotes in the format I provided and need to convert to decimal to upload to another system. Thanks "Fred Smith" wrote: There is no format which will display your examples as you want, becauseyou have text, not numbers. If you have decimal numbers for your bonds (eg, 79.75) you
can get it displayed as 32nds with: # ??/32 You will need an If formula to display what you want. Regards, Fred "novice" wrote in message ... looking for formula to convert bond price to 32nds columns have for example 79-10 which is 79 + 10/32 103-12 which is 103 + 12/32 79-10+ which is 79 + 10.5/32 103-12+ which is 103 + 12.5/32 saw a post on here about converting 32nds but not +'s

Thanks Rick - this is excellent & as I said in the other post will save me a
ton of time! I'm sure this is an issue for many others in bond world.

"Rick Rothstein" wrote:

I think this formula will do what you want...

=LEFT(A1,FIND("-",A1)-1)+MID(A1,FIND("-",A1)+1,2)/32+(RIGHT(A1)="+")/64

--
Rick (MVP - Excel)


"novice" wrote in message
...

I should clarify that my ultimate goal is to display these prices in
decimals. I am receiving a download file that quotes in the format I
provided and need to convert to decimal to upload to another system.

Thanks

"Fred Smith" wrote:

There is no format which will display your examples as you want, because
you
have text, not numbers.

If you have decimal numbers for your bonds (eg, 79.75) you can get it
displayed as 32nds with:

# ??/32

You will need an If formula to display what you want.

Regards,
Fred

"novice" wrote in message
...
looking for formula to convert bond price to 32nds

columns have for example

79-10 which is 79 + 10/32
103-12 which is 103 + 12/32

79-10+ which is 79 + 10.5/32
103-12+ which is 103 + 12.5/32

saw a post on here about converting 32nds but not +'s

Fair point, but this modification to my formula is more compact (and allows
up to 9 digits after the dash)...

=LEFT(A1,FIND("-",A1)-1)+MID(SUBSTITUTE(A1,"+",""),FIND("-",A1)+1,9)/32+(RIGHT(A1)="+")/64

--
Rick (MVP - Excel)


"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
That would work with an input like 103-09+ (with 2 digits for the number
of 32nds), but if the input could be 103-9+ you might want

=IF(RIGHT(A1)="+",1/64+LEFT(A1,FIND("-",A1)-1)+MID(A1,FIND("-",A1)+1,LEN(A1)-FIND("-",A1)-1)/32,LEFT(A1,FIND("-",A1)-1)+MID(A1,FIND("-",A1)+1,LEN(A1)-FIND("-",A1))/32)--David
Biddulph"Rick Rothstein" wrote in
. ..I think this formula
will do what you want...
=LEFT(A1,FIND("-",A1)-1)+MID(A1,FIND("-",A1)+1,2)/32+(RIGHT(A1)="+")/64
-- Rick (MVP - Excel) "novice"
wrote in
... I
should clarify that my ultimate goal is to display these prices in
decimals. I am receiving a download file that quotes in the format I
provided and need to convert to decimal to upload to another system.
Thanks "Fred Smith" wrote: There is no format which will display
your examples as you want, becauseyou have text, not numbers. If
you have decimal numbers for your bonds (eg, 79.75) you can get it
displayed as 32nds with: # ??/32 You will need an If formula
to display what you want. Regards, Fred "novice"
wrote in message
... looking
for formula to convert bond price to 32nds columns have for
example 79-10 which is 79 + 10/32 103-12 which is 103 +
12/32 79-10+ which is 79 + 10.5/32 103-12+ which is 103 +
12.5/32 saw a post on here about converting 32nds but not
+'s