Help with fractions in excel, specifically format and conver

Domer

New Member
Joined
Aug 27, 2004
Messages
4
I work with bonds which are priced in 32nds. The convention for expressing the prices is 100-12; the 100 is the whole number and the 12 is the fraction 12/32. People also denote 64th as 100-12+ which would mean 100 and 12.5 32nds or 100 25/64. My problem is this: I get my data from pdfs and paste it into excel, the data come formatted in the standard bond format convention. I need excel to recognize this convention so that I can take data in this format and enter in excel, so that excel can convert the data to decimals. I have found and use the DOLLARDE and DOLLARFR functions, but I am unable to reconcile the formatting of my data, which comes in using the hyphen. Is there a formula which will convert 100-12 to 100.12 so I can then convert it using DOLLARDE in a decimal? Second is there a way to force excel to recognize the “+” as 1/2 a 32nd ?

Thanks
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
You could use Data>Text to columns with '-' as a delimiter to seperate the two parts.

Then use the following formula:

=A1+IF(RIGHT(B1,1)="+", LEFT(B1, LEN(B1)-1)/64, B1/32)

This assumes that A1 holds 100 and B1 holds 12+
 
Upvote 0
Re: Help with fractions in excel, specifically format and co

Thanks

What I have been doing to this point is replace – with . . I had hope to find that there was a way to simply enter 100-12 and get 100.375.

Also, the + denote .5 /32 so when 12+ is written 12.5/32 is what is meant, the formula above converts 12+ into 12/64th I believe (though I am not smart enough to know where) that the 12 needs to be multiplied by 2 before being divided by 64, so the way I would write this formula if I knew how would be to take the fraction, do a logic if the + is present if it is multiple the number by 2 then add one and then divide by 64 and if the + is not present simple divide by 32. I wish I knew more about excel to put that into a formula.
 
Upvote 0
I slightly misread the post.

Try changing the formula to this

=A1+IF(RIGHT(B1,1)="+", (LEFT(B1, LEN(B1)-1)+0.5)/32, B1/32)


Is that giving the right answer.
 
Upvote 0
I think this does the trick:
book1
ABCD
1100-12100.375
2100-12+100.1875
Sheet1


Formula in B1:=LEFT(A1,FIND("-",A1)-1)+SUBSTITUTE(RIGHT(A1,LEN(A1)-FIND("-",A1)),"+","")/IF(RIGHT(RIGHT(A1,LEN(A1)-FIND("-",A1)),1)="+",64,32)

HTH
 
Upvote 0
Re: Help with fractions in excel, specifically format and co

Norie, thanks for the fix to the first formula posted, now it works.

Greg, your formula is encountering the same problem that Norie's did, I guess i didn't explain it clearly, the formula you posted returns 6/64 when 12+ is present, if some one know how to have it replace 25/64 when 12+ is present that would answer my inquire completely.
 
Upvote 0
DOH :oops: It's not like Norie just stepped right into that puddle...sheesh

B1:=LEFT(A1,FIND("-",A1)-1)+SUBSTITUTE(RIGHT(A1,LEN(A1)-FIND("-",A1)),"+",".5")/32

Note: for 1/64, the format would be 100-0+ ?? If yes, then this'll be okay.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,920
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top