Results 1 to 3 of 3

20011101, 20:00 #1the DAVE guyGuest
Dimensional Fractions (Excel 97 SR2)
Here's a formula that I use in calculating dimensions that I thought I'd share and maybe someone can help me streamline it.
I use Excel 97 to calculate dimensions that people using measuring tapes use to cut parts to length. (If I could get my company to switch to the metric system, it would make my life simpler.)
It took me a while to get fractions right. Either the denominators were wrong (try finding FourtyFive NinetySevenths on a tape measure) or the fraction wasn't reduced (Two Fourths????....That should be One Half!). Here's the solution I found. I don't like it, but it works well.
First, set the cell format to a fraction with a two decimal denominator.
Next, put this formula in the cell> =int([X]*16+.5)/16
Put any mathematical expression or cell reference in place of "[X]".
What does it do?
The formula rounds the number to the nearest 1/16" in decimal format, but the cell formatting displays it as a fraction reduced to the lowest common denominator.
Options:
Change both instances of "16" in the formula to "32" to round the number to the nearest thirtysecond of an inch. Other levels of precision work just as well.
The reason I don't like doing this is I started out long ago with MSWorks in my first computer. That program handled this operation with a simple cell format. If the guy who set up the fraction formulas in Excel were assigned to put the measurement conversions in a Betty Crocker cook book, you'd probably see conversions from tablespoons to metric tons and oven temperature setting setting conversions to British Thermal Units.
Is there a simpler way to do this?

20011102, 14:05 #2
 Join Date
 Dec 2000
 Location
 NJ, USA
 Posts
 239
 Thanks
 2
 Thanked 1 Time in 1 Post
Re: Dimensional Fractions (Excel 97 SR2)
You could use a custom function, which would take your [X] and your 16 as parameters. E.g.,
<pre>Public Function FracRound(vntInput As Variant, _
Optional intDenom As Integer = 16) As Variant
FracRound = Int(vntInput * intDenom + 0.5) / intDenom
End Function</pre>
You would still be formatting the worksheet cell(s) the way you describe, but rather than typing out your formula each time, you'd enter something like:
<pre>=FracRound(45/97)</pre>
which would display it rounded to the nearest 16th. (which I made the default), or
<pre>=FracRound(45/97,32)</pre>
if you wanted it rounded to the nearest 32nd.
Post back if you're not sure how/where the custom function is entered.

20011218, 02:20 #3
 Join Date
 Jan 2001
 Location
 West Long Branch, New Jersey, USA
 Posts
 1,937
 Thanks
 6
 Thanked 9 Times in 7 Posts
Re: Dimensional Fractions (Excel 97 SR2)
Dave,
Good job. If I understood what you're doing, you may want to look at the attached spreadsheet to see if it's of help. I used fraction formatting to set things to 16th's. I think Excel will take care of the rounding. You can even define your own denominator to some thing and Excel will express your expression to the nearest thingth. Not sure about the reducing to lowest common denominator.
Fred
PS I'm catching up with my email now.