Search code examples
google-sheetsgoogle-sheets-formulanumber-formatting

How do I use Number Formatting in Google Sheets to display an UNSIMPLIFIED, NON-reduced fraction?

I'm building a progress tracking worksheet at the moment. I use Checkboxes to mark tasks as complete, and I have a simple formula to check "how many are done (true)" / "how many there are".

=COUNTIF(M7:Z7,TRUE)/(COUNTA(M7:Z7))

This was successful, and got me the percentages I needed for my progress bars, but it wasn't as useful as I was hoping because I realized I wanted to keep track of the precise quantity of remaining items in addition to the total completion rate.

So I did some digging and found the custom number formatting options in sheets, and was able to use this format to change my calculation into a non-mixed fraction:

_# ??/??

So THAT worked just fine until I realized it was treating it like, well, an actual fraction and trying to simplify it all the time. I don't want to know that I've done 1/7th of the things, I want to know that I have done 2 out of 14 things.

The reason I noticed this problem in the first place is because when it hit "100%" for one of the task categories, it went from "13/14" to just the whole number "1," which is the opposite of what I want; I want it to still show the fraction, as "14/14."

I feel like I've been spinning my wheels for so long now, I'm not sure how much better to do this WITHOUT having to completely restructure my sheet and formulas and add some stupid hidden math columns in between what I already have built.
Ideal solutions would just be some secret in the Number Formatting options. But I absolutely cannot use a static denominator in the number formatting. At this point I'm not sure what kinds of other options there are to be honest.

tl;dr

In Google Sheets, what's the best way to display an unsimplified fraction with a non-static Numerator and Denominator?

Solution

  • If what you want is to visualize the fraction, but not use it for further calculations. You could simply display is as a string.

    For example:

    this: =COUNTIF(M7:Z7,TRUE)/(COUNTA(M7:Z7))

    becomes

    this: =COUNTIF(M7:Z7,TRUE)&"/"&COUNTA(M7:Z7)

    Note:

    Keep in mind COUNTA counts all the values in the range, not just the number of rows (Which I assume is the number of tasks). You may want to change the range passed to COUNTA to be a single column.