Displaying Fractions for Conditional Formatting

jlafavor

New Member
Joined
Sep 16, 2014
Messages
2
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?



 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

bschwartz

Well-known Member
Joined
Jan 5, 2013
Messages
1,367
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

New Member
Joined
Sep 16, 2014
Messages
2
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

Well-known Member
Joined
Jan 5, 2013
Messages
1,367
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,463
Messages
5,596,284
Members
414,051
Latest member
tabecker

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top