Excel 2007 : Weird rounding problem

[Symplystyc]

I'm having a rounding issue with excel 2007. I'll just past whats in my cells to help explain my problem.

In C7 I have a number 14.5
in another cell I have =IF(MOD(C7,1)=0.5,MROUND(C7,2),ROUND(C7,B15-1-INT(LOG10(C7))))

If Mod(C7,1) = 0.5 , checks to see if the decimal is .5
Then MROUND(C7,2) , rounds C7 to the nearest even number
Else ROUND(C7,B15-1-INT(LOG10(C7))) , Rounds C7 to a specific significant figure.

It looks to me that its saying my IF statement is false but when I past =MOD(C7,1) into a separate cell I get 0.5. and MROUND(C7,2)= 14.

I should point out that C7 is =C6*10000, so I'm wondering if my IF statement is comming up as false because C7 is an equation. I've tested my function using a cell that was not an equation and it worked just fine.

Any help would be appreciated.

Edit: also numbers like 10.5, 11.5, 12.5 etc all round the way they should with my formula the only number not working is 14.5.


Travis

[ianrats]

Hmmm... very strange!

By changing the cell format of the result of MOD(C7,1) to display 15 decimal places, the result is no longer 0.5 and hence you get the result you are from your IF statement, so the problem isn't the IF statement.

However, I think you can work around it easily enough...

Instead of B7*10000 in C7 I modified it to ROUND(B7*10000,20) assuming 20 decimal places will be enough for you although I think even at 1 decimal place it will fix the problem.

Everything then works correctly as you can see in the attached example where

Row 7 = Your original result
Row 8 = Hard coded 14.5 rather than calculated
Row 9 = Workaround using ROUND

Hope this helps

~ IAN

[Symplystyc]

I'll give that a shot once I get office installed at home. what I'm going for is trying to follow ASTM e29s rounding rules
which gives you results like this.
1.5 = 2
2.5 = 2
1.51 = 2
2.51 = 3

in my first cell I'm inputting data that will always be of the form 0.12345 then my C7 cell changes that to 1234.5 and this is where the rounding occurs. I really think my problem is that I"m not using a hard coded number in my C7 cell, but that still doesn't explain the issue with numbers like 14.5.


okay so I tried it out and it appears that what you did worked great. I'm not sure why taking the mod of this =B6*10000 gives a result of .49999999999 but thanks for finding that.
marking this as solved, if I can figure out how to.