How can I have Excel display unreduced fractions?
I have created a worksheet that I want my staff to complete as they
are progressing through a project. Their entries will reflect their
progress, for example, if they have completed eight of twenty
objectives, I want Excel to display "8/20" so that I know they had 20
objectives to begin with. If Excel reduces the fraction to 2/5, I will
not know that they actually had twenty objectives not five.

---

Request for Question Clarification by tar_heel_v-ga on 07 Sep 2004 12:47 PDT

Have you installed the Analysis Tool Pack?  It has a function that
will allow what you are looking for, dollarfr.  You can see how it
works at http://www.willowsolutions.com/tips/tips_2000_10_2.shtml

If this works, let me know and I will post as an answer.

-THV

---

Clarification of Question by vanatic-ga on 07 Sep 2004 13:04 PDT

No, that solution only works with currency, converting decimal dollars
entries into fractions with 32 as their denominator.

I need to display fractions that contain ANY denominator, and I want
Excel to leave the entries alone rather than reduce them. Some
examples of what I need follow:
If the user enters "8/20", Excel will display "8/20".
If the user enters "13/104", Excel will display "13/104".
If the user enters "4/100", Excel will display "4/100".

I know how to format cells so that Excel will convert a decimal entry
into a fraction or reduce a fractional entry into a fraction in lowest
terms (reduced), but am stumped on how to force Excel to leave a
fractional entry alone and simply display it in the cell.

---

Request for Question Clarification by tar_heel_v-ga on 07 Sep 2004 13:43 PDT

I tried this and it seems to work:

Highlight the cells you want to show these fractions.
Goto "Format", choose "Cells", then choose "Text"

The numbers you enter in the cells from that point should be read as
text, not as a number.

Now, the issue with this solution is that you cannot perform math with
the numbers in the cells. So if cell A1 is "8/20" with the format of
the cell being text, you cannot add that cell to another, for example.

---

Clarification of Question by vanatic-ga on 07 Sep 2004 14:00 PDT

I had thought of formatting the cells as text, but I want to be able
to force the user to enter their data as a fraction. As long as the
cell is formatted as a number, I can use Excel's data validation to
force them to change their entries if they enter anything but a
fraction. I lose this control if I format the cells as text.

Just a suggestion - why don't you get your staff to enter the data in
two separate cells:

e.g. 

No of tasks completed in cell A1
Total no of tasks in B1

You can then use the following formula to display the result in your
desired format: =A1&"/"@TEXT(B1,0.

The result will be displayed as text but you will be able to validate
the data originally input in cells A1 and B1.

Sorry - just spotted an error in the formula. Final character should
be a close parenthesis and not a decimal point.

Should read: =A1&"/"@TEXT(B1,0)

I really shouldn't attempt to answer questions this early in the morning!

Ignore previous formulae.

Should be: =A1&"/"&TEXT(B1,0)

Apologies!!

Try this, vanatic:

http://www.vbaexpress.com/kb/submitcode.php?kb_id=185

Sorry! Bad link. Here's a good one:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=185

Your task can be accomplished by a function - your staff can still
input "100/200" in text format into the column, for example A, then
the function can be convert it to its actual value 0.5, and store it
in an unseen column for your data validation or, do data validation by
itself.

I am not sure if it's the best solution but it will definitely work. I
cannot post the function code here so if you can advise your email I
will sent you a file as presentation.

Hi,

You'd most likely be best off keeping the format of Text.  Excel's
auto-format feature could play havoc if you're trying to control your
end-user's (non)actions.  You can always manipulate afterwards.  Of
course the best option would be (as previously suggested) keep your
data in two seperate columns.  Trying to cram more data in one cell
than should really be there does not work too well.  The more you
spread it out, the better, you shouldn't run out of room.  HTH

Take care,
Zack Barresse