convert bond price to decimal including +

  • Thread starter Thread starter novice
  • Start date Start date
N

novice

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
 
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
 
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
 
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
 
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
 
I think this formula will do what you want...

=LEFT(A1,FIND("-",A1)-1)+MID(A1,FIND("-",A1)+1,2)/32+(RIGHT(A1)="+")/64
 
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.
 
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
 
Back
Top