This question asks about how to use a formula to create truly blank cells so that they won't show up in a chart. The solution is to use the formula NA() which makes the cell take on the value #N/A.
As a common use case, I have a column which is basically a flag, containing a formula IF(*flag_condition*, 1, ""). I then have a SUM() at the top which tells me how many flags are in the column. I would then select an apparently-empty (flag=false) cell near the top of that column and use Ctrl+Down trying to jump to the next nonempty cell of that column, to inspect the values in that row, especially when the flags are sparse and the data is long.
However, Ctrl+Down just goes to the next cell, which appears empty but has the formula.
Using NA() instead of "" (a) makes the cell visibly take on the value #N/A, (b) makes the sum take on the value #N/A, and (c) does not allow CTRL+arrow to skip over that cell.
Therefore, I ask this as a separate question, which is not a duplicate of that.
Is there any solution which overcomes at least issues (c) and possibly (b) of the above?
Per request from @JvdV, here is an example:
The formula in this cell and those below is =IF(MOD(A3,2)=0,1,"").
The intended output is to press Ctrl+Down and jump to B6, instead of B4.
In this case, the flag is not particularly sparse, but in others, it is.
No comments:
Post a Comment