Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Fraction - smallest common denominator NOT wanted

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    Brainerd, MN
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hello all,
    I work with teachers, and some want to add fractions in Excel. While we understand how to format the cell to show a fraction, or fractions that are all halves, fourths, etc., is there anyway to get Excel to leave the fraction unchanged when entered? In other words, to NOT have the fraction change from 4/8 to 1/2 automatically, and without having to format individual cells. Teachers can work with many different denominators in one formula.

    Any help would be appreciated. This one has me stumped!!!

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,646
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-03-13 15:11, mbarthel wrote:
    Hello all,
    I work with teachers, and some want to add fractions in Excel. While we understand how to format the cell to show a fraction, or fractions that are all halves, fourths, etc., is there anyway to get Excel to leave the fraction unchanged when entered? In other words, to NOT have the fraction change from 4/8 to 1/2 automatically, and without having to format individual cells. Teachers can work with many different denominators in one formula.

    Any help would be appreciated. This one has me stumped!!!
    Did you try custom formatting, e.g.,

    # ??/??

    Aladin

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'm afraid there isn't a "pretty" solution to this problem, but consider this...

    1. Format column A:A with a Text number format and right horizontal alignment.
    2. Enter 4/16, 3/9, 2/4 into cells A1:A3.
    3. To sum these fractions use the array formula, {=SUM(VALUE("0 "&A1:A3))}.

    Note: Array formulas must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula.


    [ This Message was edited by: Mark W. on 2002-03-13 15:54 ]

  4. #4
    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 Mark:
    It is an interesting approach -- formatting the fractinal numbers as text to maintain the numerator and denominator exactly as entered, and then evaluating the sum of the fractional numbers using the VALUE function and the array formula -- but the use of "0" in the array formula to ensure that the fractional numbers less than 1 will be properly accounted for does not happen here -- 0 (zero) is being added to the mistaken fraction as a date, and its corresponding sequential number. So writing the fractions in your example as 0 4/16, 0 3/9, and 0 2/4 does do the trick -- thus using the array formula ... sum(value(A1:A3))will give the right result!

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-13 16:21, Yogi Anand wrote:
    Hi Mark:
    It is an interesting approach -- formatting the fractinal numbers as text to maintain the numerator and denominator exactly as entered, and then evaluating the sum of the fractional numbers using the VALUE function and the array formula -- but the use of "0" in the array formula to ensure that the fractional numbers less than 1 will be properly accounted for does not happen here -- 0 (zero) is being added to the mistaken fraction as a date, and its corresponding sequential number. So writing the fractions in your example as 0 4/16, 0 3/9, and 0 2/4 does do the trick -- thus using the array formula ... sum(value(A1:A3))will give the right result!
    My array formula, {=SUM(VALUE("0 "&A1:A3))}, works fine. Perhaps, you failed to enter the essential space character after the 0 in the text string, "0 ".

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

    Default

    THANKS MARK!
    You are RIGHT! ... I did happen to miss that essential space as in "0 " in the array formula. My apologies, I should have made sure that for 4/16 to be taken as a fraction, I had to ensure that it was read as 0 4/16, and not 04/16. THANK YOU! THANK YOU!

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
  •