Result = 0 says false????

bwesenberg

New Member
Joined
Sep 7, 2004
Messages
31
I am pulling my hair out at this point and need some help.

I have a worksheet with many cells and calculations.
I have calculations performing on results of a calculation. In then end of all of this I have a If statement that says IF(Y4=0,"Yes","No")
In the function argument box it says that Y4 is false and the result inthat cell is 0.
So it is telling me that 0 is not equal to 0.

I don't get it. This was a sheet I got from an end user and at first I thought it was her formatting so I copied the contents and pasted the values and did the calculations my self. Still same result. Then I started the whole sheet over from scratch and still same result.

If this is not clear I can post the cells and their contents. Just let me know what you need.
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,010
Office Version
  1. 365
Two things to check:

1) Is it a rounding thing where Y4 =0.000001 or something but just displays 0?

2) Is Y4 storing a text value (quick test with =Y4="0" should give you TRUE if it is)?
 

bwesenberg

New Member
Joined
Sep 7, 2004
Messages
31
I checked the rounding thing and I can't see where it would be doing that. When I started the new sheet I formatted the cells with currency, 2 decimals and not $ sign.
The cell total is 0.00 and the calc that gets that is .47-.47.

The format automatically changed itself to number with 2 decimals.

I did the test to see if it was text and it still comes back NO.
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,010
Office Version
  1. 365
With your formula that says =IF(Y4=0,"Yes","No"), if you select the Y4 part in the formula bar and press F9, what's the (new) value that's highlighted?
 

bwesenberg

New Member
Joined
Sep 7, 2004
Messages
31

ADVERTISEMENT

-6.54809539923917E-13

Ok I am not sure where that is coming from.
Is it because I am using the results of calculations to do calculations?
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,010
Office Version
  1. 365
No, it's probably not anything you're doing. It's just because Excel uses floating point arithmetic.

So, instead of calculating the value as 0, it's calculating it as -0.000000000000654809539923917

(see http://support.microsoft.com/kb/214118/en-us for more)

Just change =IF(Y4=0,"Yes","No") to =IF(ROUND(Y4,3)=0,"Yes","No") or similar
 

bwesenberg

New Member
Joined
Sep 7, 2004
Messages
31
That seemed to have worked. Thanks so much for your help. I would have pulled out all my hair soon.
 

Forum statistics

Threads
1,137,204
Messages
5,680,160
Members
419,887
Latest member
Vasokir

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top