Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Show denominator and numerator of fraction

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If I have a cell to be formatted as a fraction, is there anyway to get the denominator and numerator of the fraction.

    E.g.

    If Excel has .26163 as 45/172 in Cell A1, is there anyway to get a cell B1 to show 45 and Cell C1 to show 172?

    FYI, 3.26163 lightyears is 1 Parsec.

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    There may be a better way, but
    num
    =LEFT(TEXT(A1,"???/???"),FIND("/",TEXT(A1,"???/???"))-1)+0

    denom
    =RIGHT(TEXT(A1,"???/???"),FIND("/",TEXT(A1,"???/???"))-1)+0

  3. #3
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-16 10:11, Cosmos75 wrote:
    If I have a cell to be formatted as a fraction, is there anyway to get the denominator and numerator of the fraction.

    E.g.

    If Excel has .26163 as 45/172 in Cell A1, is there anyway to get a cell B1 to show 45 and Cell C1 to show 172?

    FYI, 3.26163 lightyears is 1 Parsec.
    I know there are probably more elegant solutions, for this one use

    in cell B1 =LEFT(A1,FIND("/",A1,1)-1)
    in cell C1 =RIGHT(A1,FIND("/",A1,1))

    HTH
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    IML,

    Thanks! Works Great!

    Yogi,

    Your formula gave me a #VALUE error message. But Thank You for trying!!

    [ This Message was edited by: Cosmos75 on 2002-04-16 11:01 ]

  5. #5
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi cosmos75:
    I did check my formula by entering
    45/172 in cell A1, then

    =LEFT(A1,FIND("/",A1,1)-1)+0 in cell B1 gives 45, and

    =RIGHT(A1,FIND("/",A1,1))+0 in cell C1 gives 172

    Please enter just the fraction 45/172 and not =45/172 in cell A1

    I would like to see what error you got ... it does work for me!


  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-16 11:13, Yogi Anand wrote:
    Hi cosmos75:
    I did check my formula by entering
    45/172 in cell A1, then

    =LEFT(A1,FIND("/",A1,1)-1)+0 in cell B1 gives 45, and

    =RIGHT(A1,FIND("/",A1,1))+0 in cell C1 gives 172

    Please enter just the fraction 45/172 and not =45/172 in cell A1

    I would like to see what error you got ... it does work for me!

    Yogi,

    .26163 is entered in a cell formatted as a fraction. Therefore it errors out when it can't find a "/" in .26163

  7. #7
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi cosmos75 and IML:
    IML's formula is more comprehensive and works when 45/172 is entered as a text string or as a number (as in =45/172). The one that I proposed works only when 45/172 is entered as a text string.

    Regards!

  8. #8
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yogi,

    .26163 is entered in a cell formatted as a fraction. Therefore it errors out when it can't find a "/" in .26163
    Hi IML:
    Yes Sir ... Agreed ... as I stated before you formula is more comprehensive and is the one I would use. T H A N K S !


  9. #9
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-16 10:11, Cosmos75 wrote:
    FYI, 3.26163 lightyears is 1 Parsec.
    Hey Cosmos,

    You sound as if you're in Astrophysics.... have you read Cliff Stohl's book The Cuckoo's Egg ? It's a good old hoot, mixing computers against a backdrop of astrophysics !


  10. #10
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Chris Davidson,

    As an undergrad I double majored in Astrophysics and Psychology (LONG STORY!!). I haven't completed my degree due to money problems. Probably just go back and finish up Psychology degree and then on to a MA/PhD Cognitive Psychology program.

    Nope, I haven't read that yet. Will look into that! Thanks for the recommendation!

Some videos you may like

User Tag List

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
  •