MattG Posted April 9, 2013 Share Posted April 9, 2013 I am trying to figure out the best way to round a value in a worksheet. I have a worksheet I have generated that creates a list of symbols with a given record and displays the fields I want in the order I want. I have two columns one labeled "=(xcoordinate)" and "=(ycoordinate)". I typically like to have my units set to Feet and Inches with a precision of 1/8" or 1/16" of a inch. Now the exception is in this report I like to have it round to the nearest inch. I do not want fractional values in the document. My initial thought was to change my database header to "=round(xcoordinate)" & "=round(ycoordinate)". However this is rounding to the nearest foot. My question after all of this is what do I need to put in my equation to have my values appear as rounded to the nearest inch without changing my units to have a precision lacking fractions. Thanks, Matt Quote Link to comment
michaelk Posted April 10, 2013 Share Posted April 10, 2013 Matt I played around with this a while ago and wasn't able to come up with a great solution. (There's probably a simple solution that eludes me.) This gets close: =CONCAT(XCOORDINATE DIV 1, '''', ' ', ROUND(SQRT(((XCOORDINATE-(XCOORDINATE DIV 1))*12)*((XCOORDINATE-(XCOORDINATE DIV 1))*12))), '"') It fails in two circumstances: 1. If the coordinate is between -1' and 0 it gives a positive value instead of negative. 2. If the coordinate is between x' 11 1/2" and( x+1)' it returns x' 12" It's probably possible to If/Then around those cases, but I gave up. example attached. MK Quote Link to comment
bcd Posted April 10, 2013 Share Posted April 10, 2013 Try this: =Round(XCoordinate*12)/12 Quote Link to comment
michaelk Posted April 10, 2013 Share Posted April 10, 2013 Yep. Do what bcd says! mk Quote Link to comment
MattG Posted April 10, 2013 Author Share Posted April 10, 2013 That did exactly what I was looking for. Thank you very much. Matt Quote Link to comment
Bryan G. Posted April 10, 2013 Share Posted April 10, 2013 This has been an issue I have had for some time. There should be a round up and round down with the ability to set rounding points. Quote Link to comment
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.