Help with fractions in excel, specifically format and conver

[Domer]

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

[Norie]

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+

[Domer]

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.

[Norie]

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.

[Greg Truby]

I think this does the trick:

******** ******************** ************************************************************************>

Microsoft Excel - book1 ___Running: xl2002 XP : OS = Windows Windows 2000
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
B1B2 =
	A	B	C	D
1	100-12	100.375	*	*
2	100-12+	100.1875	*	*
Sheet1 *

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

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

[Domer]

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.

[Greg Truby]

DOH 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.

[Domer]

THANKS!!!!!