# Rounding to .0, .5 and 1.0 Microsoft Ms office 97 standard edition... March 12, 2010 at 05:33:37
Specs: Windows XP, 3Ghz/1Gb
 Using Office 97 or Open Office (if it makes a difference)I am presently at work using Excel 2007 and tried utilizing the MROUND function but not working as desired. At home where I need it I can use Excel 97 or Open Office's Calc.If I have a number between x.1 to x.3 then round to x.0If I have a number between x.4 to x.7 then round to x.5If I have a number between x.8 to x.9 then round x+1.0Examples:12.2 rounds to 12.0 9.4 rounds to 12.58.8 rounds to 9.0Can this be done?Got a formula for that?Thanks,Bryan See More: Rounding to .0, .5 and 1.0

#1 March 12, 2010 at 06:42:27
 Hi,I used MROUND() in both Excel 2003 and 2007 and got the same results.The formula in cell B1 was:`=MROUND(A1,0.5)`Here are the results:Excel 2003``` A B 1 9.2 9.0 2 9.4 9.5 3 9.6 9.5 4 9.8 10.0 5 12.2 12.0 6 9.4 9.5 7 8.8 9.0 ```Excel 2007``` A B 1 9.2 9.0 2 9.4 9.5 3 9.6 9.5 4 9.8 10.0 5 12.2 12.0 6 9.4 9.5 7 8.8 9.0 ```Is this what you were looking for, or am I missing the point!Regards

Report •

#2 March 12, 2010 at 06:49:39
 re: 9.4 rounds to 12.5Typo?

Report •

#3 March 12, 2010 at 10:16:02
 Humar,Thank you. It works pretty close as x.3 rounds to x.5 instead of the desired x.0Otherwise it works as one would expect.The MS Rounding does round as a normal person would round numbers and the only difference is .3 in my needs.(carb counting and rounding issue related to diabetes)This is why I was wondering if there is a formula that can specify the parameters of the rounds versus a naturally built in one like MROUND.I was thinking of an IF statement but do not know how to use a wild card for the whole number such as the 4 in 4.3 (x.3). I looked in Excel Help but do not find it.BryanP.S. Is that Mround as in Mid Round?

Report •

Related Solutions

#4 March 12, 2010 at 11:58:03
 Was playing around with Brycos problem and came across this anomaly:In column A are the numbers 1.3 through 19.3In column B is the formula =MOD(A1,1) which produces the number 0.3 for every occurrence.In column C is the formula =IF(MOD(A1,1)=0.3,ROUNDDOWN(A1,0.5),MROUND(A1,0.5))Which should ROUNDDOWN all of the numbers, BUT, if you look, all of the number from 1.3 through 7.3 are rounded down but when it gets to the row containing 8.3 and above it fails.Why does this work for the numbers 1.3 through 7.3 but not for the numbers 8.3 through 19.3.Column B shows that the test should be true.``` A B C Cell 1 1.3 0.3 1 Cell 2 2.3 0.3 2 Cell 3 3.3 0.3 3 Cell 4 4.3 0.3 4 Cell 5 5.3 0.3 5 Cell 6 6.3 0.3 6 Cell 7 7.3 0.3 7 Cell 8 8.3 0.3 8.5 Cell 9 9.3 0.3 9.5 Cell 10 10.3 0.3 10.5 Cell 11 11.3 0.3 11.5 Cell 12 12.3 0.3 12.5 Cell 13 13.3 0.3 13.5 Cell 14 14.3 0.3 14.5 Cell 15 15.3 0.3 15.5 Cell 16 16.3 0.3 16.5 Cell 17 17.3 0.3 17.5 Cell 18 18.3 0.3 18.5 Cell 19 19.3 0.3 19.5 ```MIKEhttp://www.skeptic.com/

Report •

#5 March 12, 2010 at 13:01:48
 Hi Mike,I have come across issues with MOD() before.It is not accurate at about the 15th decimal place for most values.You can round the MOD() result to overcome this, although I haven't tested it much.Regards

Report •

#6 March 12, 2010 at 13:06:27
 To Bryco,Try this formula with your original number in cell A1=IF(ROUND(MOD(A1,1),3)<=0.3,INT(A1),IF(ROUND(MOD(A1,1),3)>=0.7,INT(A1)+1,INT(A1)+0.5))The formula is split onto 3 lines for ease of viewing.What it does is use modulus division to get the remiander, but 'corrects' Excel's modulus division error by using rounding.Then the IF statements decide if the remainder is 0.3 or less, or 0.7 or more, or else in-between (0.4 to 0.6)It then adds 0, 1 or 0.5 to the Integer part of the original number.Give it a try and see if it works as expected.RegardsHumar

Report •

#7 March 12, 2010 at 13:18:03
 Humar,It's not the MOD() function that's the problem.As you can see in column B, the MOD function returns the correct value.But, the IF(MOD(A1,1)=0.3, test of the MOD function returns True for the numbers 1.3 through 7.3 but then starts to return False for the numbers 8.3 through 19.3It's the IF test of the MOD function that doesn't make sense.MIKEhttp://www.skeptic.com/

Report •

#8 March 12, 2010 at 14:12:33
 Hi Mike,The MOD() result is sometimes 0.3000000000000001Hence the test for = 0.3 does not always return true.Regards

Report •

#9 March 12, 2010 at 14:50:10
 The MOD function itself never returns .300000000000001I formatted the number out over 16 places and only when you multiply it back out do you get the 1 in the 14th place.=MOD(A1,1)*10 will return 3.00000000000001MIKEhttp://www.skeptic.com/

Report •

#10 March 12, 2010 at 15:02:40
 Hi Mike,That's odd.I entered this:`=MOD(16.3,1)`and it displays as this:```0.3000000000000010 ```Also for values of 32 and above:`=MOD(32.3,1)`it displays as this:```0.2999999999999970 ```What version of Excel are you using. I did the above in 2003Regards

Report •

#11 March 12, 2010 at 16:14:40
 Humar, you are correct, my error,I failed to copy the formula down all the way. To many numbers on the page. Sorry.As for Bryco's numbers, I'm pretty sure this works:=IF(MOD(A2,1)<0.300000000000002,ROUNDDOWN(A2,0.5),MROUND(A2,0.5))MIKEhttp://www.skeptic.com/

Report •

#12 March 15, 2010 at 06:07:22
 Thank you folks very much.Will give this a try at my first opportunity and am sure it will work as designed.If it doesn't I would suspect it will because I am using Excel 97.Thanks again,Appreciate your help.Bryan

Report • 