What combination of techniques with cell formulas and pivot table configuration will enable the drill-down into the pivot table to only show rows containing the 'desireable' valued cells, ignoring the rows whose cells aspire to be empty?
I've made a fair effort to decouple my question from the solutions I've tried and focus the attention on the exact issue - pivot tables (and drill-down thereinto) behave differently with truly empty/clear/void cells than otherwise, and I can find no other 'value' that a formula can return that the pivot will treat identically to a cleared cell.
I've seen MS Graph folks meet with success using na() as the formula return for the cell and have the graphs ignore the cell, but cannot find anything that pivots will similarly ignore.
As we know, if(condition, affirmative_string, "") does NOT work, as the pivot table counts the empty string the same as one with length. Changing to if(condition, 1, 0) and configuring the pivot to summarize-by-sum is similarly not an acceptable alternative as the drill-down shows all rows contributing to the sum, even those with cells containing zero.
I've tried a UDF returning all the variations I know of for CVErr(), as well as trying to return those kinds of values as strings. I'd craft a UDF to throw a particular exception if Excel would react by clearing the cell's value (not the formula). [I'm not able to conjure an answer for why Excel wouldn't always empty the value area immediately prior to executing a formula whose interpretation was error-free, which would then enable the use of an exception-based approach, but we could all grow old trying to figure out why MS does what they do.]
Telling me a cell containing a formula is by definition not 'empty' is not constructive; Excel knows the difference itself or we wouldn't be able to have cell formulas operate on other cells whose value came from a formula. Even in the OM you can see that it stores the formula string separately from the result value.
The suggestion to manually clear/empty the cells whose formula return is the 'ignore me' value is a workaround that provides the correct pivot table values and drill-down behavior, but is not a 'solution'; it sacrifices the underlying formula and the workbook's dynamic nature to get there.
In my mind, a builtin function along the lines of "null()" would have been exactly the solution, but I can't find anything that even accidentally behaves that way.