Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 52
Default convert bond price to decimal including +

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
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: convert bond price to decimal including +

Bond Price Conversion Formula

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

Formula:
=Whole Number + (Fraction/32
For example, if you have a bond price of 79-10+, you would use the formula:

Formula:
=79 + (10.5/32
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:

Formula:
=103 + (12/32
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.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,346
Default convert bond price to decimal including +

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

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,389
Default convert bond price to decimal including +

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


  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 52
Default convert bond price to decimal including +

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





  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 52
Default convert bond price to decimal including +


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



  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,934
Default convert bond price to decimal including +

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




  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,651
Default convert bond price to decimal including +

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

  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 52
Default convert bond price to decimal including +

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


  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 52
Default convert bond price to decimal including +

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






  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,934
Default convert bond price to decimal including +

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How can I convert decimal commas to decimal points? Peteylepieu Excel Discussion (Misc queries) 1 October 2nd 07 10:18 PM
Bond prices convert to decimals J-EL Excel Discussion (Misc queries) 3 September 17th 06 12:58 AM
calculate/convert volume price to monthly average price Bultgren Excel Worksheet Functions 2 February 14th 06 10:36 AM
Calculating Bond Price legu Excel Worksheet Functions 0 January 16th 06 05:43 PM
how do you do "price of a convertible bond" ? Dennis Excel Discussion (Misc queries) 3 October 12th 05 01:45 PM


All times are GMT +1. The time now is 11:05 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"