IF + ROUNDUP hierarchy

I have two cells (A1 & B1) containing constants, and use the following formulae in C1 to calculate the next required figure, which needs to be rounded up where the decimal is 0.4 or greater:

IF(A1*B1/52-INT(A1*B1/52)>=0.4,ROUNDUP(A1*B1/52,0),ROUNDDOWN(A1*B1/52,0))

The contents of C1 is then used to calculate the value of D1, which again needs to be rounded up where the decimal is 0.4 or greater, using the following formulae:

IF(4.6*C1-INT(4.6*C1)>=0.4,ROUNDUP(4.6*C1,0),ROUNDDOWN(4.6*C1))

Up until I put some random test data into it, the logic of the formulae seemed to be fine, but I seem to have found a glitch.

When the value of A1 is 10.5 and B1 is 27631, the value of C1 rounds down to 5579, which is as expected, because the decimal is 0.3365...

However the subsequent value of D1 is rounded down to 25663, despite the decimal being 0.4 on the nose, when logically is should round up to 25664.



So far I've tried adding in extra brackets to improve the accuracy of the internal calculations, as well as adjusting the IF argument to swap the THEN and ELSE parameters around.

However the only way I've been able to get the correct value for cell D1 in the above scenario is by swapping the THEN and ELSE parameters around without adjusting the IF argument, which logically should return the inverse result that I'm after (i.e. rounds down when decimal is 0.4 or greater, otherwise rounds up).

If anybody has any experience of problems of the hierarchy in IF statements, especially when using ROUNDUP, any advice would be greatly appreciated.

Similarly if anybody can think of alternative formulae to achieve the desired result, I would be grateful.

Cheers, Pete

It has to do with Excel's precision level when evaluating functions....

Maybe try something like:

=IF(ROUND(4.6*C1-INT(4.6*C1),1)>=0.4,ROUNDUP(4.6*C1,0),ROUNDDOWN(4.6*C1,0))