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?

Improve this question

3 Answers 3

Reset to default
2

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.

Improve this answer
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.

Improve this answer
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: 

    _# ??/??

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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