Home |
Search |
Today's Posts |
#1
|
|||
|
|||
fraction format
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 |
#2
|
|||
|
|||
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" wrote in message ... 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 |
#3
|
|||
|
|||
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 "David Benson" wrote in message ... 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" wrote in message ... 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 |
#4
|
|||
|
|||
On Wed, 15 Dec 2004 22:20:11 -0600, "Horatio J. Bilge"
wrote: 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. 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" wrote in message ... 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 |
#5
|
|||
|
|||
"Jay Somerset" wrote in message ... 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 |
#6
|
|||
|
|||
On Sat, 18 Dec 2004 07:55:25 -0600, "Horatio J. Bilge"
wrote: "Jay Somerset" wrote in message ... 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. Yes -- if you want to stop reduction, you have to specify the denominator explicitly. Thanks for the info about "# ?/?". That's handy to know. You're welcome. ~ Horatio |
#7
|
|||
|
|||
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 "Jay Somerset" wrote in message ... On Sat, 18 Dec 2004 07:55:25 -0600, "Horatio J. Bilge" wrote: "Jay Somerset" wrote in message ... 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. Yes -- if you want to stop reduction, you have to specify the denominator explicitly. Thanks for the info about "# ?/?". That's handy to know. You're welcome. ~ Horatio |
#8
|
|||
|
|||
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. "Jay Somerset" wrote: On Sat, 18 Dec 2004 07:55:25 -0600, "Horatio J. Bilge" wrote: "Jay Somerset" wrote in message ... 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. Yes -- if you want to stop reduction, you have to specify the denominator explicitly. Thanks for the info about "# ?/?". That's handy to know. You're welcome. ~ Horatio |
#9
|
|||
|
|||
Hi!
=C17+D17&"/"&E17 Biff "Steven J." wrote in message ... 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. "Jay Somerset" wrote: On Sat, 18 Dec 2004 07:55:25 -0600, "Horatio J. Bilge" wrote: "Jay Somerset" wrote in message ... 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. Yes -- if you want to stop reduction, you have to specify the denominator explicitly. Thanks for the info about "# ?/?". That's handy to know. You're welcome. ~ Horatio |
#10
|
|||
|
|||
Wonderful & simple - works for all the sample situations I've tested so far.
My actual cell contents are shown below. =IF(C59+D59+E59+F590, C59+D59&"/"&C59+D59+E59+F59," ") To suppress 0/0 Thanks Biff, "Biff" wrote: Hi! =C17+D17&"/"&E17 Biff "Steven J." wrote in message ... 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. "Jay Somerset" wrote: On Sat, 18 Dec 2004 07:55:25 -0600, "Horatio J. Bilge" wrote: "Jay Somerset" wrote in message ... 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. Yes -- if you want to stop reduction, you have to specify the denominator explicitly. Thanks for the info about "# ?/?". That's handy to know. You're welcome. ~ Horatio |
#11
|
|||
|
|||
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 "Steven J." wrote in message ... Wonderful & simple - works for all the sample situations I've tested so far. My actual cell contents are shown below. =IF(C59+D59+E59+F590, C59+D59&"/"&C59+D59+E59+F59," ") To suppress 0/0 Thanks Biff, "Biff" wrote: Hi! =C17+D17&"/"&E17 Biff "Steven J." wrote in message ... 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. "Jay Somerset" wrote: On Sat, 18 Dec 2004 07:55:25 -0600, "Horatio J. Bilge" wrote: "Jay Somerset" wrote in message ... 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. Yes -- if you want to stop reduction, you have to specify the denominator explicitly. Thanks for the info about "# ?/?". That's handy to know. You're welcome. ~ Horatio |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Format numbers in chart datatable | Charts and Charting in Excel | |||
pivot chart format | Charts and Charting in Excel | |||
copy conditional format as ACTUAL format | Excel Discussion (Misc queries) | |||
cut and paste format problem | Excel Discussion (Misc queries) | |||
make a conditional format the default | Excel Discussion (Misc queries) |