4

I'm dividing the value in one cell by a value in another cell. I want the final result to be in fraction form and not reduce to the smallest equivalent fraction.
I've figured out how to get the result of the =divide to format in fraction form (# ???/???).
However, I cannot find a way to keep Google Sheets from simplifying it.

For example:
=DIVIDE(O4,M10) in my sheet should show 20/80 - but it converts to 1/4.
I want to see 20/80. The denominator will change, so I can't do this # ??/80.

It also cannot be formatted as text, because this fraction needs to be used in future math operations.

Tips?

  • yes. the fractions are equivalent, but the 20/80 is really a ratio of (points earned/total points possible). The spreadsheet later converts that fraction to a percentage. But it would be nice to see the 20/80--mathematically they are the same, but in terms of a ratio, it communicates something different. – Amanda Dawn Fischetti Mar 8 '18 at 21:50
4

Try this instead of DIVIDE,

=QUERY(O4/M10,"select Col1 format Col1 '# ?/"&M10&"'")
  • M10:Denominator
  • O4:Numerator
  • By dynamically formatting Denominator to always be M10 through QUERY,We preserve the underlying number as well as the format(as opposed to TEXT ,Where we cannot change the underlying real value.)
  • Sheets still simplifies the fraction instead of leaving it in its ratio form. – Amanda Dawn Fischetti Mar 8 '18 at 22:12
  • 3
    Bingo! Setting Number format to Automatic did the trick! Awesome Work! – Amanda Dawn Fischetti Mar 8 '18 at 22:22

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.