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.0Can this be done?

Got a formula for that?Thanks,

Bryan

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 2003A 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.0Excel 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.0Is this what you were looking for, or am I missing the point!

Regards

re: 9.4 rounds to 12.5Typo?

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?

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

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

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

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

Hi Mike, The MOD() result is sometimes 0.3000000000000001

Hence the test for = 0.3 does not always return true.

Regards

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

Hi Mike, That's odd.

I entered this:

=MOD(16.3,1)

and it displays as this:0.3000000000000010Also for values of 32 and above:

=MOD(32.3,1)

it displays as this:0.2999999999999970What version of Excel are you using. I did the above in 2003

Regards

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

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

Ask Your Question

Weekly Poll

Do you think ride and car sharing are the future of transportation?

Discuss in The Lounge

Poll History