Consulting

Results 1 to 8 of 8

Thread: Issue with Excel Division of Fractions..Not showing correct results

  1. #1

    Issue with Excel Division of Fractions..Not showing correct results

    Here is what I did..

    Generated Random Fractions in Cell B and C and added the result in Cell D. I want it to give me traditional Fraction addition, like we do in school. But its giving weird results.

    It is showing 26/29 + 0 = 68/75 !!!!!! Refer attached picture. It shall give me 26/29. (Sample Row 10)
    It is showing 2/11 + 3/7 = 11/18 !!!!! Refer attached picture. It shall give me 47/77. (Sample Row 12)

    Please let me how do i achieve traditional Fraction addition , subtraction , multiplication, division in Fractions ???
    Attached Images Attached Images
    Attached Files Attached Files

  2. #2
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,850
    Location
    Images don't help us help you. An Excel file would have helped us help you. As such, I can not replicate your problem. Check the cell formats and decimal numbers at each point.

  3. #3
    VBAX Tutor
    Joined
    Dec 2008
    Posts
    277
    Location
    It would be much easier for us to find errors if you attach an Excel file, not an image.
    They are probably rounding errors. It is also important how you generated random fractions.

    Artik

  4. #4
    Attaching excel will not help because every time i save it and re-open it, the number changes. But I am still attaching it.

    I exactly did the below -

    Step 1: In Cell B3 i typed =RAND() - This gave me a decimal.
    Step 2: In Cell C3 i typed =RAND() - This gave me a decimal.
    Step 3: In cell D3 i typed = B3 + C3 -- This gave me a decimal.
    Step 4: Now i copied B3,C3,D3 and pasted to few more cells.
    Step 5: ** Now i selected everything and changed the Format to Fraction Upto Two digits.

    You can try on your sheet and see if the addition of displayed fractions match your traditional calculations......
    Attached Files Attached Files

  5. #5
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,661
    Location
    I did a Copy/Paste Values to 'freeze' the numbers

    The fraction format is only approximate. Excel still remembers the full precision value and each cell (including the Sum) is calculated full precision, but displayed as a Fraction

    Yellow = Formatted as Fraction
    Orange = the Yellow numbers but formatted as General
    Green = Displayed fraction as full precision number (numerator / denominator)

    Capture.JPG

    Capture1.JPG
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  6. #6
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,572
    Where you're seeing 0 is because the number is too small to display Fraction|Up to 2 digits. In the picture below, the table to the right displays the decimal values of the table to the left (they're just linked). You'll see the zero in the left table is actually 0.009077:
    2019-08-25_232819.png

    If you want to see it more like you did at school you could set the workbook to Set precision as displayed:
    2019-08-25_233333.jpg

    Be aware that this permanently loses/changes your data, but you end up with:
    2019-08-25_233520.png

    Note how the values in the right table have adjusted a bit. The yellow highlighted value in the right table is now zero and you get a 'sensible' fraction in the 3rd column.

    Edit post posting: Ahh, Paul beat me to it…
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  7. #7
    Thanks a lot for your help and replies.

  8. #8
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,661
    Location
    If you want to get 'normalized' fractions like

    1/8 + 1/2 = 5/8

    or 1/2 + 70/99 = 239/198

    that's harder and won't be exact
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

Posting Permissions

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