=if rounding issue

[the running frog]

Hi,

My problem concerns rounding (I think!).

I am using the following =if formula:

=IF(BA127>=AU127,AU127,AU127-BA127)

The cells show both BA127 & AU127 as equalling 8.0, therefore I would expect the formula to return a value of 8.0. It is in fact calculating (or not) as 6.03961E-14. I have increased the decimal points on both cells and it still presents as 8.0, however when I copy & value BA127 it does so as 7.9999999888.

Is there a way I can work around this inequality i.e. by putting in an error factor of say +/- 0.001?

Many thanks in advance.

[MrShorty]

What I expect you are looking at is normal round off error caused by the way computers do arithmetic.

If 0.001 is the desired "error" tolerance, probably should be something like if(ba127>=au127-0.001,au127,au127-ba127). You have to really decide if 0.001 is the correct tolerance (would you really want 7.9991 to be considered >= 8.0000?).

[AlexMillington]

Why not combine with ROUND

=IF(ROUND(BA127,4)>=ROUND(AU127,4),AU127,AU127-BA127)

[the running frog]

Many Thanks Alex,

It worked, but due to the iterative nature of this part of my spreadsheet it has moved the problem into the next cell........

I have used the formula: =IF(AS127-AX127-AY127<AS127,AS127-AX127-AY127,AS127) where AS127 is now 15.99999999999 AX127 is 8.0000000 and AY127 also = 8.00000000. I have tried to use your rationale for rounding, but to no avail. I would expect the answer to be 0.

I hope you can assist.

Thanks.

[ben_hensel]

The cells show both BA127 & AU127 as equalling 8.0, therefore I would expect the formula to return a value of 8.0.

How are BA127 and AU127 found? Like, from some other formula?

It kinda sounds to me like you're getting the problem upstream of the IF formula.

Like, if BA127 is "7.9999999888"and then it's going off with an infinite number of trailing eights, then it will always display as "8.0" for however many arbitrary number of decimal points you display to, but it's not actually exactly 8.

Also if AU127 = 8.0 (exactly),
then I would expect the difference to be 1.12E-08, which is approximately 200,000 bigger than the 6E-14 value you're getting. (But that AU127 might be deceptively displaying as 8.0000 if all the trailing digits are greater than 5!)

So I don't think either of your AU127 or BA127 values are exactly 8.0 there.

This might be a computer arithmatic error, but I suspect you've got more going on than just that.

[AlexMillington]

Two options:

1) presumably AS, AY, BA etc all contain formulae. If so encapsulate all these formulae with ROUND(...,3)
2) encapsulate the logical tests in your IF formulae with ROUND(...,3)

For example: =if(round(logical test,3)<round(logical test 2,3),do this if true,do this if false)

Let me know how you get on

[the running frog]

Many thanks Alex,

It worked!

Love learning new things.

Neil
the running frog
[I]transforming performance[/I]

[AlexMillington]

Always a pleasure