fraction format

[Horatio J. Bilge]

I asked about this earlier, but I'll try again, with less rambling...

Is there a way to format fractions so that they do not reduce to the lowest
common denominator?

For example,
0/4 does not become 0/1
2/4 does not become 1/2
4/4 does not become 1/1

Thanks,
~ Horatio

 

[David Benson]

Horatio,

If you want your fractions to always have a specific denominator, then yes,
there is a way.

Select the cell or range you want to format, then click Format > Cells... .
Next, click Custom. Enter format spec such as "# ?/4". The "#" specifies a
variable number of whole-number digits. The "?" specifies a single numerator
digit. The "/" is the division line. The "4" forces the denominator always
to be four.

If you select a denominator with more than one digit (for example,
sixteenths), be sure that the numerator has the same number of "?'s".

David

 

[Horatio J. Bilge]

Thanks for the tip. This worked well for some fractions (e.g., 2/4 did not
reduce to 1/2). However, 0/4 and 4/4 reduced to 0 and 1, respectively. I
removed the "#", and it worked well (format "?/4").

Also, the denominator is not always the same. For some columns, the
denominator is 4, for some it is 3, and others it is 2. I can change the
format for each column separately, but is there a way to use the same format
for all of them?

Thanks,
~ Horatio

 

[Jay Somerset]

Thanks for the tip. This worked well for some fractions (e.g., 2/4 did not
reduce to 1/2). However, 0/4 and 4/4 reduced to 0 and 1, respectively. I
removed the "#", and it worked well (format "?/4").

Also, the denominator is not always the same. For some columns, the
denominator is 4, for some it is 3, and others it is 2. I can change the
format for each column separately, but is there a way to use the same format
for all of them?

Try the format "?/?" for a 1-digit denominator ("??/??" for a 2-digit
denominator, etc.) The "# ?/?" format does the same thing, but allows for
numbers greater than or equal to 1 (e.g. 2-1/4). That was why you got a "1"
earlier.

 

[Horatio J. Bilge]

Try the format "?/?" for a 1-digit denominator ("??/??" for a 2-digit
denominator, etc.) The "# ?/?" format does the same thing, but allows for
numbers greater than or equal to 1 (e.g. 2-1/4). That was why you got a "1"
earlier.

With the format "?/?" fractions still reduce automatically. For example, 2/2
becomes 1/1, and 2/4 becomes 1/2.
Thanks for the info about "# ?/?". That's handy to know.

~ Horatio

 

[Jay Somerset]

With the format "?/?" fractions still reduce automatically. For example, 2/2
becomes 1/1, and 2/4 becomes 1/2.

Yes -- if you want to stop reduction, you have to specify the denominator
explicitly.

 

[David McRitchie]

You might take a look at
Fractions rounded to 1/4, 1/8, 1/16, 1/32, 1/64, 1/128
http://www.mvps.org/dmcritchie/excel/fractex1.htm
but regardless of whether you have a number or a string
as the result you need an extra column for the display.

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

 

[Guest]

I am entering sport statistics. A player may make a number of attempts
with any number of them successful (4/4, 10/12, 0/9, etc.). I have one
column that shows the percentage of successful attempts. Players want to
know the actual number of successes over attempts. I created another column
with the formula shown below.

=IF(ISERROR((C17+D17)/SUM(C17:E17)), 0, (C17+D17)/SUM(C17:E17))

This formula adds two columns of successful attempts (perfect & good)
divided by the total of four columns (perfect, good, poor, bad). It also
suppresses the divide by zero error.

Like Horatio, I'm interested in having the actual numbers show in the
fraction without reductions (4/4 not reduced to 1/1 or 1, 10/12 not reduced
to 5/6, etc.). There are no standard denominators, so that line of
formatting will not work for me.

Any suggestions? Please!!!

Steven J.

 

[Biff]

Hi!

=C17+D17&"/"&E17

Biff

I am entering sport statistics. A player may make a number of attempts
with any number of them successful (4/4, 10/12, 0/9, etc.). I have one
column that shows the percentage of successful attempts. Players want to
know the actual number of successes over attempts. I created another
column
with the formula shown below.

=IF(ISERROR((C17+D17)/SUM(C17:E17)), 0, (C17+D17)/SUM(C17:E17))

This formula adds two columns of successful attempts (perfect & good)
divided by the total of four columns (perfect, good, poor, bad). It also
suppresses the divide by zero error.

Like Horatio, I'm interested in having the actual numbers show in the
fraction without reductions (4/4 not reduced to 1/1 or 1, 10/12 not
reduced
to 5/6, etc.). There are no standard denominators, so that line of
formatting will not work for me.

Any suggestions? Please!!!

Steven J.

 

[Guest]

Wonderful & simple - works for all the sample situations I've tested so far.
My actual cell contents are shown below.

=IF(C59+D59+E59+F59>0, C59+D59&"/"&C59+D59+E59+F59," ") To suppress 0/0

Thanks Biff,

 

[Biff]

You're welcome! Thanks for the feedback.

I run a little NFL Football contest and keep track of a persons won/loss
record. Just like what you're doing except I use this format: W - L.

Biff