Copper Contributor

Hello,

I'm using Excel 2016 in Finnish and encountered a problem with empty cells treated as zeros in my sheet. I'm trying to create a simple way to visualize a set of numbers fed into the sheet as they are fed. I already tackled the three Y-axis - problem but the empty cell problem is getting me.

There are good tutorials about using #N/A instead of "" or zero which should make the graph go straight to the next available point omitting the empty data. After a long search I found out the Finnish version's equivalent is #PUUTTUU! ("missing"). When I type it on the part of the sheet not yet filled I can have nice line graphs ending at the last filled in data.

But when I use a formula, i.e. =JOS(B14="";"#PUUTTUU!";B14*2) and it returns the value #PUUTTUU! the graph handles this differently drawing them as zeros. (JOS means IF)

And..this is where it gets weird: If I do a paste special and copy one of the returned values without any formulas or formatting to any cell and then copy this "raw" #PUUTTUU! -text back replacing the cells containing the formula I get the same zero-problem i had in the beginning. If I type the exact same text myself and copy it to the same cells it works as intended.

ANSWER TO MYSELF: I copied this to word and back to excel and it worked as intended. Removing the parenthesis did the trick when checking alternatives.

3 Replies

@JyrkiT Not sure I follow everything, but have you selected the option to show empty cells as "connect data points with line"? See the picture below.

There actually is a function that returns #NA!. The function is called NA() in English Excel. So if you start typing PUU into a formula then Excel should show the function's proper name to you.

My problem was solved when I changed the original formula

=JOS(B17="";"#PUUTTUU!";B17*2)

to the format

=JOS(B17="";#PUUTTUU!;B17*2)

Some metadata must've come with the first version that confused the program. I got this hint from a programmer friend who absolutely knows nothing about excel but a lot about problemsolving.