Cell format - fractions

[Matt]

I have a set of cells that show the fraction of #complete/#total. They
are formatted as fractions so that I can custom format them such that
those >=50% turn green and those <50% turn red. The problem is that
for a completion of, say 8/12, excel automatically simplifies to 2/3.
Is is possible for excel to still recognize the cell as a fraction but
not simplify?

 

[Guest]

You can directly control this by formatting in advance. Let's say you enter
the data in twelths and want it to remain that way:

Format > Cells... > Number > Custom and then enter

# ??/12

Experiment with formats like this. There is a lot more available than just
the standard fraction formats.

 

[Ron Rosenfeld]

I have a set of cells that show the fraction of #complete/#total. They
are formatted as fractions so that I can custom format them such that
those >=50% turn green and those <50% turn red. The problem is that
for a completion of, say 8/12, excel automatically simplifies to 2/3.
Is is possible for excel to still recognize the cell as a fraction but
not simplify?

Here's one approach:

Format the cell as TEXT with a RED font (or however you want the <50%
formatted)

Use this Conditional Format to format the cell green:

Format/Conditional Format/Formula Is:

=LEFT(A1,FIND("/",A1)-1)/MID(A1,FIND("/",A1)+1,5)>=0.5


Another approach would be to enter the numerator and denominator in two
different cells.

Then use VBA to custom format the display cells as fractions with the
appropriate numerator (e.g. ??/#total) and also custom format the cell with the
desired color.


--ron