Show denominator and numerator of fraction
Amazing chart utilities from Jon Peltier
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
    357
    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
    357
    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
    357
    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!

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
  •  

 

 
DMCA.com