This isn't a major issue, but I was wondering if anyone had any alternatives to offer.
I have a chart that contains null or missing values. When my boss exports the chart to excel, sometimes he wants to add up selected cells across a row using the arithmetic operator '+' and paste that same formula down the column. The problem is when one of those cells contain a null value excel returns the #VALUE! error.
I've tried replacing the null and missing symbols within the chart with nothing, but when the chart exports to excel it's not equivalent to an empty cell.
(There's an over-simplified example attached, export the chart to excel and try =B2+D2, you'll get #VALUE!, but try =D2+ any cell outside the table and it will actually calculate a value for you)
Replacing the null symbol with '0' works, but for aesthetic reasons we don't want a bunch of 0s all over the chart. I've also tried changing text values to match the background color in the chart (so that the null values represented by '0' are there, but just can't be seen) and I'm not so happy with that just because once it's in excel if someone happens to accidentally overwrite the cell value with a different value you won't see it unless you have the cell selected.
Dose anyone have any suggestions? Thanks!
