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

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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)?
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
-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?
 
Upvote 0
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
 
Upvote 0
That seemed to have worked. Thanks so much for your help. I would have pulled out all my hair soon.
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

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
Back
Top