Excel how to keep fraction as fraction 5/5 not 1 or 0/5 not 0

0
votes

I have a sheet that counts the positive cells in a range, and all the non blank cells in the same range. It displays the result as a fraction. I would like to stop excel from displaying fractions as whole numbers, e.g., 5/5 not 1, 0/2 not 0, 0/5 not 0. Below is an example of what I'm trying to accomplish.

The formula in column F is as follows: =COUNTIF(A2:E2,">0")/COUNTIF(A2:E2,"<>")

Column G shows how I would like the results displayed.

enter image description here

2
excelexcel-formulaformattingformulafractions
Why don't you treat all as fractions of 5? Would make life a lot easier when you custom numberformat everything to ?/5 – JvdV

2 Answers

1
votes

Enclose the / in quotes and concatenate with &.

=COUNTIF(A2:E2,">0")&"/"&COUNTIF(A2:E2,"<>")
0
votes

Try this way: You need to format the cell/column where you get the result of your formula. Let's say it is Cell "B6". Choose "Fraction" in the "Number" section of the "Home" ribbon. Right-click your mouse and choose "Format cells". Go to "Custom" and make your own format. It can be something like this "???/???" or like this "###/###". I hope it will help you.