convert bond price to decimal including +

[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

 

[Shane Devenshire]

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

 

[Fred Smith]

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]

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

 

[novice]

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

 

[Rick Rothstein]

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

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

 

[David Biddulph]

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" <[email protected]> wrote in messagethink 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" <[email protected]> wrote in messageI 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" <[email protected]> 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>>>>>>>

 

[novice]

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

 

[novice]

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]

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