Rounding Measurements Down To Nearest 1/8th Inch

[polaris900]

Hi All,

I seem to be having some trouble with rounding fractions. I frequently take down measurements from a device and puts them on an excel spreadsheet as text. (thats just the recording stage). I then take those numbers and what I need to do is be able to round these measurements down to the nearest 1/8 inch.
Examples of measurements I receive below

57 19/32 should round down to 57 1/2 inches
69 9/16 should round down to 69 1/4 inch
75 15/16 should round down to 75 7/8 inches
30 25/32 should round down to 30 3/4 inches


I have tried using the cell format function but what happens id when I set to the 1/8th inch numbers like 4/8 come up which should be displayed as 1/2. I am just looking for a quick easy way to convert these measurements.

If someone could help I would be very grateful.

[shg]

You're going to need a formula: =floor(a1, 1/8) and then format as 0 ?/?

[Moo the Dog]

This should do the trick:

=FLOOR(A1,0.125)

- Moo

* I see the Texas Kid beat me to it... although I don't think you will need to worry about formatting.. at least with Mac 2011 it automatically inherits the formatting of the cell being referenced in the formula.

Oh, and your second example:

69 9/16 should round down to 69 1/4 inch

is incorrect... that rounds down to 69 1/2.

[polaris900]

Thx Moo the dog <--Quite the name.. And SHG you are correct I made a mistake.

So I can get the formula to work if I input manually in each cell and it works great. But For some reason or other I can not copy and paste the measurements into these cells with out it causing issues. Any Ideas?? I have uploaded the file where I would like to copy the measurements from and then paste them into this new formula to have them rounded. Have a look and let me know what you think. I have tried to format as number or fraction then paste but it won't work. Thanks ahead of time guys.jenny sundquist measurements.xls

[Moo the Dog]

You don't paste them "into" the formula... you would have this formula in other cells (for instance, in another column, next to the originals).

Also, the measurements in your sheet are all text values, they are not numbers. To use a rounding function on them they will need to be converted to numbers first. And I am not entirely sure of a simple way to do that, other than re-typing the values in the cells.

- Moo