Forum Discussion
ROUNDING FORMULA TO NEAREST INCH IN DECIMAL FORM
Try
=CEILING(B12*B3, 1/12)
If you want to round that to 2 decimal places, use
=ROUND(CEILING(B12*B3, 1/12), 2)
Try
=CEILING(B12*B3, 1/12)
If you want to round that to 2 decimal places, use
=ROUND(CEILING(B12*B3, 1/12), 2)
- There's one more thing I'd like to ask about. You're formula =ROUND(CEILING(B12*B3, 1/12), 2) works perfectly with most of the calculations except for when the answer should be #.66. The program wants to keep saying that it's .67 instead. Is there a way to correct it and make it say .66 instead?
8 inches = 2/3 feet = 0.66666666... feet.
Rounded to 2 decimal places. this becomes 0.67, not 0.66
But if you really want 0.66. you could use
=ROUNDDOWN(CEILING(B12*B3, 1/12), 2)
- I just tried that and it seems to throw off more of the numbers. I think I can just deal with the 2 outliers .17 and .67. I will just inform my team that they should expect that to happen and to adjust them to .16 and .66 respectively.
Thanks again for your help. You've helped me add some icing to this project.
LORDPHILLIP if what you mean is you want to round up to nearest inch fraction but then truncate (i.e. round down) after 2 digits then just use ROUNDDOWN
=ROUNDDOWN(CEILING(B12*B3, 1/12), 2)
here is a table for comparisons
inches decimal rounded roundDown 1 0.083333 0.08 0.08 2 0.166667 0.17 0.16 3 0.25 0.25 0.25 4 0.333333 0.33 0.33 5 0.416667 0.42 0.41 6 0.5 0.5 0.5 7 0.583333 0.58 0.58 8 0.666667 0.67 0.66 9 0.75 0.75 0.75 10 0.833333 0.83 0.83 11 0.916667 0.92 0.91 12 1 1 1
- That's fantastic! Thank you so much for your help!
- That got me closer thank you. Here's what it just did. I changed the value of one of the cells and the answer it gave me this time is 584.9167. That's much closer than before, but I still need it to round it so that it says 584.92. Any thoughts?
why 1/12? i think the OP wants nearest 1/4 inch
=CEILING(B12*B3, 1/4)ohhhh, these are measurements in feet. I have no idea how you knew that. then yes your formulas make sense now
