Excel suppressing zero problem almost solved ...but a serious setback, please help.

%3CLINGO-SUB%20id%3D%22lingo-sub-1313035%22%20slang%3D%22en-US%22%3EExcel%20suppressing%20zero%20problem%20almost%20solved%20...but%20a%20serious%20setback%2C%20please%20help.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1313035%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20using%20Excel%202016%20in%20Finnish%20and%20encountered%20a%20problem%20with%20empty%20cells%20treated%20as%20zeros%20in%20my%20sheet.%20I'm%20trying%20to%20create%20a%20simple%20way%20to%20visualize%20a%20set%20of%20numbers%20fed%20into%20the%20sheet%20as%20they%20are%20fed.%20I%20already%20tackled%20the%20three%20Y-axis%20-%20problem%20but%20the%20empty%20cell%20problem%20is%20getting%20me.%3C%2FP%3E%3CP%3EThere%20are%20good%20tutorials%20about%20using%20%23N%2FA%20instead%20of%20%22%22%20or%20zero%20which%20should%20make%20the%20graph%20go%20straight%20to%20the%20next%20available%20point%20omitting%20the%20empty%20data.%20After%20a%20long%20search%20I%20found%20out%20the%20Finnish%20version's%20equivalent%20is%20%23PUUTTUU!%20(%22missing%22).%20When%20I%20type%20it%20on%20the%20part%20of%20the%20sheet%20not%20yet%20filled%20I%20can%20have%20nice%20line%20graphs%20ending%20at%20the%20last%20filled%20in%20data.%3C%2FP%3E%3CP%3EBut%20when%20I%20use%20a%20formula%2C%20i.e.%26nbsp%3B%3DJOS(B14%3D%22%22%3B%22%23PUUTTUU!%22%3BB14*2)%20and%20it%20returns%20the%20value%20%23PUUTTUU!%20the%20graph%20handles%20this%20differently%20drawing%20them%20as%20zeros.%20(JOS%20means%20IF)%3C%2FP%3E%3CP%3EAnd..this%20is%20where%20it%20gets%20weird%3A%20If%20I%20do%20a%20paste%20special%20and%20copy%20one%20of%20the%20returned%20values%20without%20any%20formulas%20or%20formatting%20to%20any%20cell%20and%20then%20copy%20this%20%22raw%22%20%23PUUTTUU!%20-text%20back%20replacing%20the%20cells%20containing%20the%20formula%20I%20get%20the%20same%20zero-problem%20i%20had%20in%20the%20beginning.%20If%20I%20type%20the%20exact%20same%20text%20myself%20and%20copy%20it%20to%20the%20same%20cells%20it%20works%20as%20intended.%3C%2FP%3E%3CP%3EIf%20this%20made%20any%20sense%20and%20You%20have%20a%20hint%20how%20to%20get%20around%20this%20problem%2C%20please%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1313035%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1313165%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20suppressing%20zero%20problem%20almost%20solved%20...but%20a%20serious%20setback%2C%20please%20help.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1313165%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F624643%22%20target%3D%22_blank%22%3E%40JyrkiT%3C%2FA%3E%26nbsp%3BNot%20sure%20I%20follow%20everything%2C%20but%20have%20you%20selected%20the%20option%20to%20show%20empty%20cells%20as%20%22connect%20data%20points%20with%20line%22%3F%20See%20the%20picture%20below.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202020-04-16%20at%2017.06.18.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F184778i248A69D07287882A%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Screenshot%202020-04-16%20at%2017.06.18.png%22%20alt%3D%22Screenshot%202020-04-16%20at%2017.06.18.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1313171%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20suppressing%20zero%20problem%20almost%20solved%20...but%20a%20serious%20setback%2C%20please%20help.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1313171%22%20slang%3D%22en-US%22%3EThere%20actually%20is%20a%20function%20that%20returns%20%23NA!.%20The%20function%20is%20called%20NA()%20in%20English%20Excel.%20So%20if%20you%20start%20typing%20PUU%20into%20a%20formula%20then%20Excel%20should%20show%20the%20function's%20proper%20name%20to%20you.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1313256%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20suppressing%20zero%20problem%20almost%20solved%20...but%20a%20serious%20setback%2C%20please%20help.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1313256%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20problem%20was%20solved%20when%20I%20changed%20the%20original%20formula%3C%2FP%3E%3CP%3E%3DJOS(B17%3D%22%22%3B%22%23PUUTTUU!%22%3BB17*2)%3C%2FP%3E%3CP%3Eto%20the%20format%3C%2FP%3E%3CP%3E%3DJOS(B17%3D%22%22%3B%23PUUTTUU!%3BB17*2)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESome%20metadata%20must've%20come%20with%20the%20first%20version%20that%20confused%20the%20program.%20I%20got%20this%20hint%20from%20a%20programmer%20friend%20who%20absolutely%20knows%20nothing%20about%20excel%20but%20a%20lot%20about%20problemsolving.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22JyrkiT_0-1587050627692.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F184786iD4CD9C97E029629F%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22JyrkiT_0-1587050627692.png%22%20alt%3D%22JyrkiT_0-1587050627692.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New 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.

If this made any sense and You have a hint how to get around this problem, please help.

 

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
Highlighted

@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.

 

Screenshot 2020-04-16 at 17.06.18.png

Highlighted
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.
Highlighted

@Riny_van_Eekelen 

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.

JyrkiT_0-1587050627692.png