Share via

How do I keep Excel from changing my fractions?

Anonymous
2012-08-31T20:12:45+00:00

I work for a lumber company and I need to use the fraction 4/12 as a pitch. I can not get Excel to stop reducing the fraction. Is there a way around it?

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Anonymous
    2012-09-01T02:03:13+00:00

    If your denominator will always be 12, you can use a custom format:

    Format/Cells/Number Custom Type:  # ??/12

    If your denominator can vary, probably the simplest would be to either format the cell as text before making your entry; or preceding your entry by a single quote.

    20+ people found this answer helpful.
    0 comments
Answer accepted by question author
  1. Anonymous
    2012-09-01T07:55:29+00:00

    Do you need to use the 4/12 (or any other pitch values) as a number to do other calculations with?

    If not, then Ron's suggestion to make the column Text or type the entries in with a leading apostrophe like

    '4/12

    is probably best.  The ' will not display in the cell, but will tell Excel to consider 4/12 as text.

    10+ people found this answer helpful.
    0 comments

12 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2014-09-30T14:43:07+00:00

    If your denominator is dynamic, here is an example to explain another solution:

    Column A has guest names. Column B has an "X" next to the guest name if they RSVPed. Cell C1 has this formula to show "RSVPed Guests / Total Guests":

    =COUNTA(A:A)**&"/"&**COUNTA(B:B)

    The formula can be adjusted to fit any scenario. Ampersands (&) allow you to concatenate formulas/strings/values.

    10+ people found this answer helpful.
    0 comments
  2. Anonymous
    2015-02-03T02:29:08+00:00

    This is broken in Excel 365.  Setting the custom format to # ??/4 does nothing and 8/4, for example, will always be reduced and displayed as 2.  I have found no way to force it to stop displaying the reduced values if those values are integers.

    10 people found this answer helpful.
    0 comments
  3. Anonymous
    2015-02-03T14:53:37+00:00

    If you want to ensure that a number is always displayed with a denominator of 4, then use the custom format:   ?/4

    If you want something else, be more specific.

    Thank you.  I understand that, internally, the value is stored as a float.  What I am saying is that, in Excel 365, the specified custom format is ignored.  

    Try it.  Format an empty cell it as you suggest.  Then enter 4/4 and watch as 1 is displayed.  Enter 8/4 and watch as 2 is displayed.  There is no way to force the value to be displayed as a fraction if it reduces to an integer. 

    I need to be able to use the value in calculations, so formatting as text is a bother (one has to give the cell a name and do the calculation when defining the name).

    I consider this inconsistent behavior (display the entered value as a fraction unless it reduces to an integer) to be a bug...either in the code or in the functional specification.  Users want consistent behavior and do not want to have to figure out workarounds to obtain results that they reasonably expect to get.

    Thanks again.

    4 people found this answer helpful.
    0 comments