Displaying Fractions for Conditional Formatting

[jlafavor]

Hello,

I am trying to help my wife with a school data table. She wants to conditionally format the boxes within the table to color code them, the problem is that she wants the data set displayed as a fraction, which is only difficult because there are some kids that have 0/7 correct or 7/7 correct which do not format well when using number formatting. Any ideas?

 

[bschwartz]

Hi jlafavor,

Welcome to the forums.

If you want to format numbers as fractions, you need to enter them as decimals, and then format the cells to be fractions. Otherwise, you need to first format the cells to be fractions, and then enter the data as fractions. It appears that your fractions may be formatted as text, since they presently left-aligned.

Assuming that they are stored as numbers, 0's do align left when formatted as a fraction. I assume this is because the term 0/4 is not a number, it's simply zero, so Excel reads it as text. This can be corrected by aligning the whole column to the right.

What is the conditional formatting you are trying to use, and where is the trouble originating?

 

[jlafavor]

I want them colored based on the legend on the right, so 0-49% would be red, etc.

Yes, they are currently formatted as text using '7/7, the problems are that once I set the format to be as fractions when I enter 0/5 it automatically converts it to text and when I enter 7/7 it displays 1. My wife likes the display of the 7/7 rather than a decimal or any other display. Which is where the problem lies.

 

[bschwartz]

I totally understand. The only problem is that once you enter the fractions as text, you can't do numerical calculations on them. Similarly, you won't be able to conditionally format based on a percentage (which would require Excel to recognize 3/7 as a number equal to 42.9%).

What if you had two columns for each test? One for the # of questions answered correctly, and one for the number of questions on the test. You could then combine them into a third column if you wanted, to get the percentage, or you can conditionally format based on the division of the two cells.