3

I'm trying to put some marks in a spreadsheet and have a lot of unsimplified fractions or fractions like 19.5/20. Is there a way to enter fractions and have the cells be usable in other formulas, while keeping them displayed as originally entered?

1

Put the numerator and denominator in different cells. Use an extra column to calculate the decimal representation of the fraction (eg d2 = b2/cd) to use in calculations, and convert the original cells to text for display use.

1

Use the format option :

enter image description here

or Format -> Number -> More Formats -> Custom number format...

Solution 1 :
enter image description here
Use 0.0"/20" as a custom format on the cell containing 19.5.
WARNING : It has still the value 19.5 but is display as 19.5/20.

Solution 2
enter image description here
Use the ??/20 custom format on a cell with =19.5/20

Long story short : it has the value 19.5/20 = 0.975 but will always display a round numerator with /20 denominator. I.E. 20/20 here. Having decimals within a fraction is way more complicated and is another question.

1

To my experience most methods fail.

The one I find most useful is:

  1. Write the fraction as a formula: =19/20

  2. Under Format -> More Formats->Custom number format use:

    _# ??/??

  • I tried this, although it simplified things like 19.5/20 to 39/40 and 90/100 to 9/10 :( – Akababa Sep 6 at 0:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.