Convert measurements to 1/16 inches

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

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

Reply | Quote

Ken wants a result in feet, inches and sixteenth inches. Your formula returns feet and sixteenth feet – not quite the same.

Reply | Quote

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.

Reply | Quote

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.

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

Reply | 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

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

Reply | Quote