Fraction - smallest common denominator NOT wanted

[mbarthel]

Hello all,
I work with teachers, and some want to add fractions in Excel. While we understand how to format the cell to show a fraction, or fractions that are all halves, fourths, etc., is there anyway to get Excel to leave the fraction unchanged when entered? In other words, to NOT have the fraction change from 4/8 to 1/2 automatically, and without having to format individual cells. Teachers can work with many different denominators in one formula.

Any help would be appreciated. This one has me stumped!!!

 

[Aladin Akyurek]

On 2002-03-13 15:11, mbarthel wrote:
Hello all,
I work with teachers, and some want to add fractions in Excel. While we understand how to format the cell to show a fraction, or fractions that are all halves, fourths, etc., is there anyway to get Excel to leave the fraction unchanged when entered? In other words, to NOT have the fraction change from 4/8 to 1/2 automatically, and without having to format individual cells. Teachers can work with many different denominators in one formula.

Any help would be appreciated. This one has me stumped!!!

Did you try custom formatting, e.g.,

# ??/??

Aladin

 

[Mark W.]

I'm afraid there isn't a "pretty" solution to this problem, but consider this...

1. Format column A:A with a Text number format and right horizontal alignment.
2. Enter 4/16, 3/9, 2/4 into cells A1:A3.
3. To sum these fractions use the array formula, {=SUM(VALUE("0 "&A1:A3))}.

Note: Array formulas must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula.
This message was edited by Mark W. on 2002-03-13 15:54

 

[Yogi Anand]

Hi Mark:
It is an interesting approach -- formatting the fractinal numbers as text to maintain the numerator and denominator exactly as entered, and then evaluating the sum of the fractional numbers using the VALUE function and the array formula -- but the use of "0" in the array formula to ensure that the fractional numbers less than 1 will be properly accounted for does not happen here -- 0 (zero) is being added to the mistaken fraction as a date, and its corresponding sequential number. So writing the fractions in your example as 0 4/16, 0 3/9, and 0 2/4 does do the trick -- thus using the array formula ... sum(value(A1:A3))will give the right result!

 

[Mark W.]

On 2002-03-13 16:21, Yogi Anand wrote:
Hi Mark:
It is an interesting approach -- formatting the fractinal numbers as text to maintain the numerator and denominator exactly as entered, and then evaluating the sum of the fractional numbers using the VALUE function and the array formula -- but the use of "0" in the array formula to ensure that the fractional numbers less than 1 will be properly accounted for does not happen here -- 0 (zero) is being added to the mistaken fraction as a date, and its corresponding sequential number. So writing the fractions in your example as 0 4/16, 0 3/9, and 0 2/4 does do the trick -- thus using the array formula ... sum(value(A1:A3))will give the right result!

My array formula, {=SUM(VALUE("0 "&A1:A3))}, works fine. Perhaps, you failed to enter the essential space character after the 0 in the text string, "0 ".

 

[Yogi Anand]

THANKS MARK!
You are RIGHT! ... I did happen to miss that essential space as in "0 " in the array formula. My apologies, I should have made sure that for 4/16 to be taken as a fraction, I had to ensure that it was read as 0 4/16, and not 04/16. THANK YOU! THANK YOU!