Rounding values to nearest 0.5
Posted by Tim B on February 19, 2001 12:42 PM
I am working on a spreadsheet for reporting lab results on a certain test. I need to reference a value in another cell (no problem) and report it rounded to the nearest 0.5 (this part is making me crazy). So 145.2 would display 145.0 and 145.4 would display 145.5. Is this possible? Dummy-level answer preferred :-) Thanks.
Posted by Aladin Akyurek on February 19, 2001 1:33 PM
Would
=IF(A1-INT(A1)>0.25,CEILING(A1,0.5),FLOOR(A1,0.5))
do what you want?
Aladin
Posted by Tim B on February 19, 2001 2:58 PM
Thanks for the reply. That formula seems to work for numbers between N.00 and N.50, and from N.75 to N+1, but not between N.50 and N.75 As in: 145.4 and 145.6 should both return a value of 145.5, but 145.6 actually returns 146.0. That's closer than I got, though!
Tim
Posted by Ian on February 19, 2001 3:35 PM
I am working on a spreadsheet for reporting lab results on a certain test. I need to reference a value in another cell (no problem) and report it rounded to the nearest 0.5 (this part is making me crazy). So 145.2 would display 145.0 and 145.4 would display 145.5. Is this possible? Dummy-level answer preferred :-) Thanks.
How about
=IF(A1-INT(A1)<0.25,+INT(A1),IF(A1-INT(A1)<0.75,+INT(A1)+0.5,+INT(A1)+1))
It seems to work, but I'd double check it carefully if you have any negative values.
Good luck
Posted by Tim B on February 19, 2001 4:25 PM
Posted by Tim B on February 19, 2001 4:29 PM
Oops, hit return too soon and sent a duplicate post. Yes, that formula works great...negative numbers are not an issue in this case. Thanks!
Tim
Posted by Celia on February 20, 2001 1:05 AM
I am working on a spreadsheet for reporting lab results on a certain test. I need to reference a value in another cell (no problem) and report it rounded to the nearest 0.5 (this part is making me crazy). So 145.2 would display 145.0 and 145.4 would display 145.5. Is this possible? Dummy-level answer preferred :-) Thanks.
Tim
If negative values are not an issue, you can use : =MROUND(A1,0.5)
(Note:- Analysis ToolPak needs to be installed.)
Celia
