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.0
If I have a number between x.4 to x.7 then round to x.5
If I have a number between x.8 to x.9 then round x+1.0
Examples:
12.2 rounds to 12.0
9.4 rounds to 12.5
8.8 rounds to 9.0

Can this be done?
Got a formula for that?

Thanks,
Bryan


See More: Rounding to .0, .5 and 1.0

Report •

#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.5

Typo?


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.0
Otherwise 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.

Bryan

P.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.3

In 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

MIKE

http://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.

Regards

Humar


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.3

It's the IF test of the MOD function that doesn't make sense.

MIKE

http://www.skeptic.com/


Report •

#8
March 12, 2010 at 14:12:33
Hi Mike,

The MOD() result is sometimes 0.3000000000000001

Hence 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 .300000000000001
I 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.00000000000001

MIKE

http://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 2003

Regards


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))

MIKE

http://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 •

Ask Question