Results 1 to 8 of 8

Help with fractions in excel, specifically format and conver

This is a discussion on Help with fractions in excel, specifically format and conver within the Excel Questions forums, part of the Question Forums category; I work with bonds which are priced in 32nds. The convention for expressing the prices is 100-12; the 100 is ...

  1. #1
    New Member
    Join Date
    Aug 2004
    Posts
    4

    Default Help with fractions in excel, specifically format and conver

    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

  2. #2
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    66,269

    Default

    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+
    If posting code please use code tags.

  3. #3
    New Member
    Join Date
    Aug 2004
    Posts
    4

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

  4. #4
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    66,269

    Default

    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.
    If posting code please use code tags.

  5. #5
    MrExcel MVP
    Int'l Moderator
    Greg Truby's Avatar
    Join Date
    Jun 2002
    Location
    39 17' 15" N, -94 40' 26" W
    Posts
    9,770

    Default

    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
    =

    A
    B
    C
    D
    1
    100-12100.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
    Greg

    Work: XL 2003, 2007 and 2010 on Windows 7
    Please use CODE tags - especially for longer excerpts of code.

  6. #6
    New Member
    Join Date
    Aug 2004
    Posts
    4

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

  7. #7
    MrExcel MVP
    Int'l Moderator
    Greg Truby's Avatar
    Join Date
    Jun 2002
    Location
    39 17' 15" N, -94 40' 26" W
    Posts
    9,770

    Default

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

    Work: XL 2003, 2007 and 2010 on Windows 7
    Please use CODE tags - especially for longer excerpts of code.

  8. #8
    New Member
    Join Date
    Aug 2004
    Posts
    4

    Default Re: Help with fractions in excel, specifically format and co

    THANKS!!!!!

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com