Exporting blank values to excel! #VALUE! error.

[nikita23]

I have a straight table and pivot that contains null or missing values.  When user want  to exports the tables to excel, sometimes he/she 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 tables with nothing, but when the tables 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 tables .  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 in advance!!

[marcus_sommer]

Using the normal operators to perform a calculation is often not the best choice. Better will be your users use:

=sum(B2;D2) instead of =B2+D2

- Marcus

[Sue_Macaluso]

@nikita23 Are you using QlikView or Sense?  I would like to move this into the correct product forum. Thank you

[nikita23]

Hi Sue Macaluso,

I am using QlikSense.

[nikita23 · Accepted solution]

Thanks Marcus!!

but it has been resolved by using the expressions

if(isnull(Month), '',Month )

because there was a gap in my expression between ' ' like this thats why it was consider this as a character.