Very strange problem, is this a bug?

[excelos]

Hello!

These two functions seem identical to me:

=IF(D46<$J8,IF(D29=0,IF(D30=0,0,"BOTTLENECK"),D29/($J8-COLUMNS($D49:D49))),0)
=IF(D46<$J9,IF(D32=0,IF(D33=0,0,"BOTTLENECK"),D32/($J9-COLUMNS($D50:D50))),0)

The input cells are exactly the same, D29=0 and D30=431 with the only difference being $J8 being 28 and $J9 being 30.

However, the second function delivers inconsistent results with the first one.

Any idea what could be wrong?

 

[Peter_SSs]

Re: Very strage problem, is this a bug?

Is there a way to upload the file to check?

Not to the forum, but you can upload to a free file-share site (eg Dropbox) and provide a shared link here in the forum.

 

[RoryA]

Re: Very strage problem, is this a bug?

How do I check that?

On the formulas tab, click the Calculation options button on the right and see what it's set to. If it's automatic already, perhaps the cell isn't really exactly 0.

 

[Scott T]

Re: Very strage problem, is this a bug?

How do I check that?

In the formulas tab you will se Calculation Options. Click on it and make sure automatic is selected.

 

[excelos]

Re: Very strage problem, is this a bug?

In the formulas tab you will se Calculation Options. Click on it and make sure automatic is selected.

Yes, it is automatic.

This issue drives me crazy.

Is there a spreadsheet website I can paste all the formulas? I cannot upload the file.

Thanks

 

[RoryA]

Re: Very strage problem, is this a bug?

Does G32 contain a formula (if so, what), or a typed 0 value?

 

[excelos]

Re: Very strage problem, is this a bug?

Does G32 contain a formula (if so, what), or a typed 0 value?

G32 contains a formula:

[]=IF((F30+F32+G31-$C$9)>=0,F30+F32+G31-$C$9,0)[]

 

[excelos]

Re: Very strage problem, is this a bug?

Oh I found it!

The cell is actually 0.00000000000000023523423462!
I did check few decimals but didn't go to that level!

Thanks all

 

[joeu2004]

Re: Very strage problem, is this a bug?

G32 contains a formula:
[]=IF((F30+F32+G31-$C$9)>=0,F30+F32+G31-$C$9,0)[]

The remedy (and simplification) is:

=MAX(0, ROUND(F30+F32+G31-$C$9,2))

Change the rounding precision (2) to whatever number of decimal places that you expect to be accurate. Do not choose an arbitrary number of decimal places like 10, as some people suggest.

 

[Peter_SSs]

Re: Very strage problem, is this a bug?

The cell is actually 0.00000000000000023523423462!

Points to Rory.

.. perhaps the cell isn't really exactly 0.

 

[excelos]

Re: Very strage problem, is this a bug?

Points to Rory.

Indeed, and I did check 0.0000000 but not 0.000000000234! :D