# Thread: Dimensional Fractions (Excel 97 SR-2)

1. ## Dimensional Fractions (Excel 97 SR-2)

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 Fourty-Five Ninety-Sevenths 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 thirty-second 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?

2. ## Re: Dimensional Fractions (Excel 97 SR-2)

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.

3. ## Re: Dimensional Fractions (Excel 97 SR-2)

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 thing-th. Not sure about the reducing to lowest common denominator.

Fred

PS I'm catching up with my email now.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•