Forum Discussion
Using IF(ISBLANK getting #Value! error
brendababy02 Try this:
=IF(C4="","",E3-C4)With =ISBLANK(B4) an empty cell returns TRUE in C4, otherwise FALSE. In your case the formula results in "", which is not empty, when B4 contains nothing (i.e. is blank). Thus, the formula always executes the FALSE argument, E3 - "" (a number minus a text). Hence, #VALUE!
I set "show zero" option back, with that the reason of the issue is more clear
For R1 formula returns zero, not empty string
As a comment, even if it returns empty string, ISBLANK() to test it doesn't work. Cell is blank only if nothing returned to it. Any formula returns some value, zero or empty string or some number in your case.
In next sheet reference returns zero which is hided by your formatting option,
Again, ISBLANK(Worksheet!R1) is always FALSE as far as you have any formula in it. Thus formula in D5 is equivalent of =Worksheet!R1. If in H5 in condition you compare with zero, not with empty string, you have desired result.
juliejo , you are welcome.
As a comment, I'd do not recommend hiding zero option in settings. Instead you may apply custom number format only to the cells where you don't like to show zeroes (after the testing) like
General;General;
where instead of first/second General could be desired number or currency format for positive and negative values accordingly.