Search:

# 2,22045E-16

Asked By: Lourdes    Date: Sep 30    Category: MS Office    Views: 2028

My concern is not about VBA this time.

But I hope someone here could help me.

Plain Excel surprised me a lot last week.

One of my formulas subtract 1.6 from 1.6.

An what I expect out of the operation is obviously 0.

And what I get is 2.22045E-16 !

I tried to google it. The only thing I learnt on www.hpmuseum.org forum is
that

Xcel 2002 - as they say - has a bug when using parenthesis.

I cannot imagine any formula without at least two.

I tried to put the formula in different way.

I checked both figures changing their format to a number with 30 decimal
places.

Then they are no more no less but 1.6 followed by 29 zeros.

Still don't know why zero is so hard to obtain this time.

Share:

What exactly is the formula you're using? I tried "=1.6-1.6" and it
worked just fine.

Definitely looks like a rounding problem. What you see may look like 1.6,
but internally it may be something like 1.5999999999999999999999999. For
display purposes, Excel would display 1.6.

Accountants run into this a lot when they use formulas  to calculate monetary
amounts and then add the columns of numbers. What is displayed may be
correct to the last zloty, but in fact contain fractions, that are not
reflected by the cell format.

How are these values calculated?

This is very crude solution, but you might add a
formula that says if value is smaller than some value
accept it as zero.
for example
=if(a1<0.000001,0,a1)

we might use similar logic for seeing the smaller
fraction.
For example a1-cell formula is 0.00001
and we are screening as 0
the range for showing non zero solution is 0.001
the in a2=a1*10=0
a3=a2*10=0.001
so by this way we can find the exact number.
However this is laborious and might not be effective
if you want to apply.

Rather than correct a symptom, the discrepancy should be fixed where it is
created. Otherwise, the discrepancy will propagate from other cells
referencing the problem cell, and other cells referencing these cells and...

I managed to make up the workaround and get highly desirable zero.

I try to answer all your posts concerning the problem in a fcfs
manner.

The original formula was:

=IF(BH3=0;"N";IF(BK3-AE\$12<=0;"P";BK3-AE\$12))
repeated 1499 times in BM3:BM1502

The part of the formula which should return 0 but returned 2.22045E-16
is BK3-AE\$12.
Referenced value in column BK3:BK1502 is the increasing amount of
sent messages
of analyzed billing calculated with =IF(BH3=1;SUM(\$BJ\$3:BJ3);0).
AE\$12 is a place where a user puts the value of the messages pack.
It can be 10 20 or 50. The cost of a single message can be 0.1 0.2 or
0.4.

Definitely it is not a rounding problem then.
The values the formula returns include between -50 and 50 and it is
calculated with adding or subtracting 10 20 50 0.1 0.2 0.4.
All is calculated in a default number format so I can see 2.22045E-16
at once.

After a day of extensive search I have tried all I could think of to
get rid of 2.22045E-16. I have run out of ideas and finally the
approach of that hp calc fan I mentioned in my original post have
worked: Do not use parenthesis – he says (here I can be
extravagant
and put even two ; ).

I changed original formula
=IF(BH3=0;"N";IF(BK3-AE\$12<=0;"P";BK3-AE\$12))
to
=IF(BH3=0;"N";IF(BI3<=0;"P";BI3))
and put
BK3-AE\$12
in
BI3:BI1502

It works. Discrepancy reduced to zero.
And definitely I have learnt how much the zero is.

Bug found? Maybe.

So remember no parenthesis and keep calm.

Didn't find what you were looking for? Find more on 2,22045E-16 Or get search suggestion and latest updates.