Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts
    This may have been asked and I am just not looking for the right stuff.

    I have a spreadsheet where the user enters a measurement in inches, and it converts the measurement to Feet, Inches and Sixteenths of an inch. The problem is when they enter a measurement that is not a multiple of 1/16 (.0625) the results are not acceptable.

    I was thinking that I could use a VLookup or some function to round the number to the closest 1/16th. However, I am not sure how to use the lookup function when there is no direct match. On the other hand, there may be a simpler method that I haven't thought of.

    I have a attached a copy of the spreadsheet for a better idea of what I am attempting to accomplish.

    Any ideas are greatly appreciated.

    Ken
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You can use

    =ROUND(16*C7,0)/16

    to round C7 to the nearest multiple of 1/16, then apply your formula to convert to feet and inches.

  3. #3
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='kwvh' post='786554' date='28-Jul-2009 23:13']This may have been asked and I am just not looking for the right stuff.

    I have a spreadsheet where the user enters a measurement in inches, and it converts the measurement to Feet, Inches and Sixteenths of an inch. The problem is when they enter a measurement that is not a multiple of 1/16 (.0625) the results are not acceptable.

    I was thinking that I could use a VLookup or some function to round the number to the closest 1/16th. However, I am not sure how to use the lookup function when there is no direct match. On the other hand, there may be a simpler method that I haven't thought of.

    I have a attached a copy of the spreadsheet for a better idea of what I am attempting to accomplish.

    Any ideas are greatly appreciated.

    Ken[/quote]


    Hi Ken

    See if the attached is what you are after.

    the formula I have use is

    =CONVERT(C7,"in","ft")

    and the cell is formatted :
    right click on the cell
    choose format cells
    select Fraction
    choose As sixteens (8/16) under Type
    OK

    Tp ose the convert function, Amalysis Toolpak must be enable in the Addins
    Attached Files Attached Files
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Ken wants a result in feet, inches and sixteenth inches. Your formula returns feet and sixteenth feet - not quite the same.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    BTW, I don't think your intermediate formula in D4 is correct. It should be

    =INT(C7-C4*12)

    You can use

    =ROUND(E7*16,0)

    in E4.

  6. #6
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    [quote name='HansV' post='786566' date='28-Jul-2009 11:42']BTW, I don't think your intermediate formula in D4 is correct. It should be

    =INT(C7-C4*12)

    You can use

    =ROUND(E7*16,0)

    in E4.[/quote]

    Ken:

    Don't know if this matters but if the user is entering data for some type of order and you always want to send the order plus extra rounded to the 1/16 you may want to use the RoundUp function otherwise the customer will get less than expected. On the other hand if its always important to send the customer less rounded ot the 1/16 because that is what the customer expects (the item will fit in the desired space) you would need to use RoundDown function.

    Tom Duthie

  7. #7
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts
    [quote name='duthiet' post='786587' date='28-Jul-2009 14:59']Ken:

    Don't know if this matters but if the user is entering data for some type of order and you always want to send the order plus extra rounded to the 1/16 you may want to use the RoundUp function otherwise the customer will get less than expected. On the other hand if its always important to send the customer less rounded ot the 1/16 because that is what the customer expects (the item will fit in the desired space) you would need to use RoundDown function.

    Tom Duthie[/quote]
    Hans, Franz and Tom,

    THANKS! The Round function worked fine with a single exception. If the sixteenths was over 15.5 it resulted in 16/16ths. Added some IF(,,) to address it and it works fine.

    Thank you all.

    Ken

Posting Permissions

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