Menu
Possible Duplicate:
Return empty cell from formula in Excel
I have an IF statement. If a cell = n, then do something, else output NULL
=IF(A1=5, "Success", NULL) // #NAME?
=IF(A1=5, "Success", "NULL") // NULL (as in text, not actually NULL!)
=IF(A1=5, "Success", "") // blank but not NULL
=IF(A1=5, "Success", 0) // zero value but not NULL
919
To do this, type a space before the equal sign when you enter the formula. You can also use the null value, which is represented by two double quotes ("""). If you want to return a blank cell, you can use the ISBLANK function. This function returns TRUE if the cell is blank, and FALSE if it is not blank.
You cannot write a null value to a cell. You use an empty string instead, like in the previous point. ISBLANK() tests if a cell is empty. As far as I can see in your examples your cells have formulas and so will never be empty and so you have no use ffor ISBLANK() in this case.
Use the IF function to do this. Use a formula like this to return a blank cell when the value is zero: =IF(A2-A3=0,””,A2-A3)
As you've indicated, you can't output NULL in an excel formula. I think this has to do with the fact that the formula itself causes the cell to not be able to be NULL. "" is the next best thing, but sometimes it's useful to use 0.
--EDIT--
Based on your comment, you might want to check out this link. http://peltiertech.com/WordPress/mind-the-gap-charting-empty-cells/
It goes in depth on the graphing issues and what the various values represent, and how to manipulate their output on a chart.
I'm not familiar with VSTO I'm afraid. So I won't be much help there. But if you are really placing formulas in the cell, then there really is no way. ISBLANK() only tests to see if a cell is blank or not, it doesn't have a way to make it blank. It's possible to write code in VBA (and VSTO I imagine) that would run on a worksheet_change event and update the various values instead of using formulas. But that would be cumbersome and performance would take a hit.
160
I've been frustrated by this problem as well. Find/Replace can be helpful though, because if you don't put anything in the "replace" field it will replace with an -actual- NULL. So the steps would be something along the lines of:
1: Place some unique string in your formula in place of the NULL output (i like to use a password-like string)
2: Run your formula
3: Open Find/Replace, and fill in the unique string as the search value. Leave "replace with" blank
4: Replace All
Obviously, this has limitations. It only works when the context allows you to do a find/replace, so for more dynamic formulas this won't help much. But, I figured I'd put it up here anyway.
29
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
