Results 1 to 3 of 3
  1. #1
    the DAVE guy
    Guest

    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. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    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. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    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.
    Attached Files Attached Files

Posting Permissions

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