Fraction - smallest common denominator NOT wanted

mbarthel · Mar 13th, 2002, 04:11 PM

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 · Mar 13th, 2002, 04:15 PM

Quote:

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. · Mar 13th, 2002, 04:49 PM

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 · Mar 13th, 2002, 05:21 PM

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. · Mar 13th, 2002, 05:34 PM

Quote:

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 · Mar 13th, 2002, 05:50 PM

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!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 13th, 2002, 04:11 PM	#1
mbarthel New Member Join Date: Mar 2002 Location: Brainerd, MN Posts: 1	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!!!

Mar 13th, 2002, 04:49 PM	#3
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	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 ]

Mar 13th, 2002, 05:21 PM	#4
Yogi Anand MrExcel MVP Join Date: Mar 2002 Location: Michigan USA Posts: 11,452	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!

Mar 13th, 2002, 05:50 PM	#6
Yogi Anand MrExcel MVP Join Date: Mar 2002 Location: Michigan USA Posts: 11,452	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!