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?

1
  • 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. Mar 8, 2018 at 21:50

1 Answer 1

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.)
2
  • Sheets still simplifies the fraction instead of leaving it in its ratio form. Mar 8, 2018 at 22:12
  • 3
    Bingo! Setting Number format to Automatic did the trick! Awesome Work! Mar 8, 2018 at 22:22

Your Answer

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

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