ruler fractions
Rich
I enter a not of fraction in cells which I have formula
that add, subtract,mulitply these fraction. But I want the
fraction always to be a ruler fraction (1/32, 3/16, 5/64.
etc) and not a mathematical formula (3/49, 7/48, etc.). Is
there any third party formula I can use?
Thanks
rich
Ron Rosenfeld
wrote:
You can use an Excel formula -- no need for a third party formula.
It's merely a matter of rounding your result to the nearest "ruler
fraction". So first decide how far you want to round the fraction, and
then apply the following formula:
=mround(A1, 1/32) where 1/32 should be changed to the smallest "ruler
fraction" you'd want to see.
If this function is not available, and returns the #NAME? error, install
and load the Analysis ToolPak add-in.
1. On the Tools menu, click Add-Ins.
2. In the Add-Ins available list, select the Analysis ToolPak box, and
then click OK.
3. If necessary, follow the instructions in the setup program.
or, if you don't want the Analysis Tool Pak:
=ROUND(F2*fraction,0)/fraction
where fraction represents the inverse of the desired fraction. E.g:
2,4,8,16,32,64,128, etc.
Format the cell something like # ???/???.
--ron
David McRitchie
Fractions rounded to 1/4, 1/8, 1/16, 1/32, 1/64, 1/128
http://www.mvps.org/dmcritchie/excel/fractex1.htm
For instance example in cell B5
converting 26.0827 to a simple fraction 26 3/32
would be as follows.
=TEXT(A1,"0" &IF(ABS(A1-ROUND(A1,0))>1/MAX(2,4,8,16,32)/2, "
0/"&CHOOSE(MATCH(MIN(ABS(ROUND(A1*{2,4,8,16,32},0)-A1*{2,4,8,16,32})/{2,4,8,16,32}),ABS(ROUND(A1*{2,4,8,16,32},0)-A1*{2,4,8,16,32})/
{2,4,8,16,32},0),2,4,8,16,32),""))
All that extra code is to reduce fractions to
16ths, 8ths, 4ths, halves as well as needed.
But also the page covers formulas for handling feet and inches as well.
not as simple as it sounds.
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
"Ron Rosenfeld" <ronros...@nospam.org> wrote in message news:q2l53vcpjmb80k9vc...@4ax.com...
Harlan Grove
>I think Bernie Deitrick's formulas would be of interest for this one.
>Fractions rounded to 1/4, 1/8, 1/16, 1/32, 1/64, 1/128
>http://www.mvps.org/dmcritchie/excel/fractex1.htm
>
>For instance example in cell B5
>converting 26.0827 to a simple fraction 26 3/32
>would be as follows.
>
>=TEXT(A1,"0"&IF(ABS(A1-ROUND(A1,0))>1/MAX(2,4,8,16,32)/2,
>" 0/"&CHOOSE(MATCH(MIN(ABS(ROUND(A1*{2,4,8,16,32},0)
>-A1*{2,4,8,16,32})/{2,4,8,16,32}),ABS(ROUND(A1*{2,4,8,16,32},0)
>-A1*{2,4,8,16,32})/{2,4,8,16,32},0),2,4,8,16,32),""))
>
>All that extra code is to reduce fractions to
>16ths, 8ths, 4ths, halves as well as needed.
I never saw this before. Did anyone else ever notice the subexpression
MAX(2,4,8,16,32)
which will *always* return 32. Is there a reason for this? Now, if it had
been written
MAX({2,4,8,16,32})
then the array constant would match all the other array constants, so *all*
could easily be replaced by a defined name, but as written it just looks
like wasted cycles.
Then there's the peculiar rounding behavior. A1=26+1/64 is rendered as "26"
rather than "26 1/32" (rounding up), A1=26+{3,4,5}/64 are all rendered as
"26 1/16", while TEXT(26+1/64,"0 ?/32") returns "26 1/32" and
TEXT(26+5/64,"0 ?/32") returns "26 3/32".
Perform arithmetic rounding rather than relying on formatting for rounding.
Then all it takes is checking if there's any benefit from using a second
digit in the fractional part's denominator. All ruler fractions to 1/64s
could be handled by the template formula
=TEXT(ROUND(A1/RFrac,0)*RFrac,"0 ?/"
&IF(ABS(TEXT(ABS(A1),"0 ?/??")-A1)<ABS(TEXT(ABS(A1),"0 ?/?")-A1),
"??","?"))
where RFrac could be 1/2, 1/4, . . ., 1/64, even 1/3, 1/5, 1/12, etc. For
ruler fractions to 1/128s, use the template formula
=TEXT(ROUND(A1/RFrac,0)*RFrac,"0 ?/"
&IF(ABS(TEXT(ABS(A1),"0 ?/???")-A1)<ABS(TEXT(ABS(A1),"0 ?/??")-A1),
"???",IF(ABS(TEXT(ABS(A1),"0 ?/??")-A1)<ABS(TEXT(ABS(A1),"0 ?/?")-A1),
"??","?")))
which is significantly shorter than
=TEXT(A1,"0" &IF(ABS(A1-ROUND(A1,0))>1/MAX(2,4,8,16,32,64,128)/2,
" 0/"&CHOOSE(MATCH(MIN(ABS(ROUND(A1*{2,4,8,16,32,64,128},0)
-A1*{2,4,8,16,32,64,128})/{2,4,8,16,32,64,128}),ABS(ROUND(A1*
{2,4,8,16,32,64,128},0)-A1*{2,4,8,16,32,64,128})/{2,4,8,16,32,64,128},0),
2,4,8,16,32,64,128),""))
or
=TEXT(A1,"0" &IF(ABS(A1-ROUND(A1,0))>1/128/2,
" 0/"&CHOOSE(MATCH(MIN(ABS(ROUND(A1*{2,4,8,16,32,64,128},0)
-A1*{2,4,8,16,32,64,128})/{2,4,8,16,32,64,128}),ABS(ROUND(A1*
{2,4,8,16,32,64,128},0)-A1*{2,4,8,16,32,64,128})/{2,4,8,16,32,64,128},0),
2,4,8,16,32,64,128),""))
and requires one fewer level of nested function calls.
--
Public Service Announcement:
Don't attach files to postings in this newsgroup.
Ron Rosenfeld
wrote:
>For instance example in cell B5
>converting 26.0827 to a simple fraction 26 3/32
>would be as follows.
>
>=TEXT(A1,"0" &IF(ABS(A1-ROUND(A1,0))>1/MAX(2,4,8,16,32)/2, "
>0/"&CHOOSE(MATCH(MIN(ABS(ROUND(A1*{2,4,8,16,32},0)-A1*{2,4,8,16,32})/{2,4,8,16,32}),ABS(ROUND(A1*{2,4,8,16,32},0)-A1*{2,4,8,16,32})/
>{2,4,8,16,32},0),2,4,8,16,32),""))
I've seen that code posted before but don't understand the advantage of it
over:
=MROUND(A1,1/32)
or, if you don't have/want the analysis toolpak:
=ROUND(A1*32,0)/32
(both formatted as # ??/??)
????
--ron
Harlan Grove
...
>I've seen that code posted before but don't understand the advantage of it
>over:
>
>=MROUND(A1,1/32)
>
>or, if you don't have/want the analysis toolpak:
>
>=ROUND(A1*32,0)/32
>
>(both formatted as # ??/??)
>
>????
Too true. It serves no useful purpose.
The only thing using "?/?", "?/??", "??/??, "?/???", "??/???" or "???/???"
do is vary alignment, but that might be important sometimes, so there may be
some need for varying the fractional format.
Ron Rosenfeld
Depending on the desired alignment, one could also use something like
"# ###/###" or develop the format with a function dependent on the
denominator.
--ron
Harlan Grove
>"# ###/###" or develop the format with a function dependent on the
>denominator.
First point - YES! Second point - NO! Actually the format "# #/###" would be
sufficient in all cases in which the value was rounded to a multiple of the
finest desired fraction, i.e., 1/N for some integer N. Developing a format
dependent on the denominator requires far more work than it's worth since it
does nothing that the "?/?", "#/#", "?/#" and "#/?" format variations don't.
David McRitchie
Disadvantage of # #/## formatting,
A1: 26
B2: =MROUND(A1/32) formatted as # #/##
displays in B2 as 26 0/1
I think I can dispense with fixing the MAX with the following
for 32ths to show something like 26 instead of 26 0/1
but normally show numbers like 26 3/8 or 46 29/32
=IF(ABS(A1-ROUND(A1,0))<1/64,TEXT(A1,"0 "),TEXT(ROUND(A1*32,0)/32,"0 #/##"))
---------------- Examples for 1/4, 1/8, 1/16, 1/32, 1/64 -----------
for 4ths
=IF(ABS(A1-ROUND(A1,0))<1/8,TEXT(A1,"0 "),TEXT(ROUND(A1*4,0)/4,"0 #/##"))
for 8ths
=IF(ABS(A1-ROUND(A1,0))<1/16,TEXT(A1,"0 "),TEXT(ROUND(A1*8,0)/8,"0 #/##"))
for 16ths
=IF(ABS(A1-ROUND(A1,0))<1/32,TEXT(A1,"0 "),TEXT(ROUND(A1*16,0)/16,"0 #/##"))
for 32nds
=IF(ABS(A1-ROUND(A1,0))<1/64,TEXT(A1,"0 "),TEXT(ROUND(A1*32,0)/32,"0 #/##"))
for 64ths
=IF(ABS(A1-ROUND(A1,0))<1/128,TEXT(A1,"0 "),TEXT(ROUND(A1*64,0)/64,"0 #/##"))
---
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
"Ron Rosenfeld" <ronros...@nospam.org> wrote in message news:v1293vsoht6nmp0ai...@4ax.com...
Ron Rosenfeld
wrote:
>Hi Harlan and Ron,
>Disadvantage of # #/## formatting,
> A1: 26
> B2: =MROUND(A1/32) formatted as # #/##
>displays in B2 as 26 0/1
No, No, No. It sure doesn't display that way on my version of Excel --
Excel 2002 (10.4524.4219) SP-2.
I think you may have made some typos. There clearly is a typo in your
version of the MROUND formula above. But I get the following:
A1: 26
B2: =mround(A1,1/32)
Format of B2: "# #/###" (or any of the variations we've discussed)
B2 shows: 26
(Perhaps you accidentally formatted it as "# 0/##") -- if not, there's
something very odd going on and we should look into it.
--ron
Ron Rosenfeld
>"Ron Rosenfeld" <ronros...@nospam.org> wrote...
>...
>>Depending on the desired alignment, one could also use something like
>>"# ###/###" or develop the format with a function dependent on the
>>denominator.
>
>First point - YES! Second point - NO! Actually the format "# #/###" would be
>sufficient in all cases in which the value was rounded to a multiple of the
>finest desired fraction, i.e., 1/N for some integer N. Developing a format
>dependent on the denominator requires far more work than it's worth since it
>does nothing that the "?/?", "#/#", "?/#" and "#/?" format variations don't.
That makes sense. I really hadn't thought the second point through
clearly, as I had not come up against something where it might be an issue.
Best,
--ron
David McRitchie
You are absolutely correct, sorry it takes so long to sink in
Certainly has an advantage over strictly text in that they can
be used arithmetically.
As you originally stated, without my typos
=MROUND(A1,1/32) formatted as # #/##
=ROUND(A1*32,0)/32 formatted as # #/##
or format as 0 #/## to prevent problems with dates
if exported to .csv and reimported elsewhere. Though I
know Excel 2002 has better facilities to prevent non-dates
from being forced to show as dates.
---
HTH, My Excel is 2000 and previous was Excel 95, all
of this was available in Excel 95 as well, as I remember.
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
"Ron Rosenfeld" <ronros...@nospam.org> wrote in message news:jd793vcq8cks5h55v...@4ax.com...
Ron Rosenfeld
wrote:
>or format as 0 #/## to prevent problems with dates
>if exported to .csv and reimported elsewhere.
Good point about the '0'.
--ron
Harlan Grove
>"David McRitchie" <dmcri...@msn.com> wrote:
>>Hi Harlan and Ron,
>>Disadvantage of # #/## formatting,
>> A1: 26
>> B2: =MROUND(A1/32) formatted as # #/##
>>displays in B2 as 26 0/1
>
>No, No, No. It sure doesn't display that way on my version of Excel --
>Excel 2002 (10.4524.4219) SP-2.
To back up Ron, it doesn't do what David says in Excel 2000 (9.0.2720) or
Excel 97 SR-2. In both, =MROUND(26,1/32) [presumably what David meant to
write] formatted as "0 #/###" displays as 26 with no fractional part.
David McRitchie
sorry for not being more specific about what was corrected with the
correct formula.
---
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
"Harlan Grove" <hrl...@aol.com> wrote in message news:OPUvUIdxCHA.1288@TK2MSFTNGP11...