CODEWITHSUNDEEP
excelexcel-formulaformattingformulafractions
wraith
wraith

Reputation: 380

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

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

Upvotes: 0

Views: 3051

Answers (2)

Ashgabat
Ashgabat

Reputation: 81

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.

Upvotes: 0

BigBen
BigBen

Reputation: 49998

Enclose the / in quotes and concatenate with &.

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

Upvotes: 1

Related Questions