Search: | |||||

| ||||

Home » Forum » MS Office | RSS Feeds |

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.

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)

it is not an exact answer to the question asked. But

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.

Related Topics: