Forum Discussion
Line charts show zero or #N/A value as zero not as gap even after checking the hidden cell box.
I am trying to create a simple Line chart for and not have zero or #N/A show up on the chart. I have watched a bunch of videos and tried several different formals to hide the points but the still show up. Not a problem if I choose bar graphs but I don't want bar graphs. I have set the hidden empty cell data and it made no change.
10 Replies
- To hide #NV, you can use Conditional Formatting,
Manage Rules,
New Rule,
Use the formula to determine the cells to be formatted,
Insert the formula "= ISTNV (B2)" and Format the cell as desired.
or with VLOOKUP ... small example below.
Sheet with name 'Tabelle3'
A B
1 A #NV
2 B #NV
3 C #NV
Cell Formel
B1 =SVERWEIS(A1;E:F;2)
B2 =SVERWEIS(A2;E:F;2)
B3 =SVERWEIS(A3;E:F;2)
Cell Conditional Formatting... Format
B1 1: =ISTNV(B1) abc
B2 1: =ISTNV(B2) abc
Short answer if it worked would be good: -) ... even if it didn't work ... You can never stop learning 🙂
Nikolino smherring4 If I understand your requirement correctly, you want the line chart to show a gap where your value is either 0 or #N/A.
The workaround is below:
1. In a column (preferably next to your horizontal series column), add this formula:
=IF(OR(C2=0,ISNA(C2)),NA(),C2)
where C is your original value column, to force all 0 and #N/A to be an error #N/A
2. Create a line chart with your horizontal series column and the new column created by Step 1
3. Select the new column with formula in Step 1, press F5 then click on "Special", tick "Formulas" and "Errors" only (i.e. untick all other options under Formulas and keep only "Errors" ticked)
3. While all cells showing #N/A in the new column are selected by Step 2, press Delete
The result will be reflected immediately in your line chart with gap where you want. Attached workbook is a sample.
I added your formula and followed the steps and got a No cells Found response and the same chart I had before.
My original formula
=IF(AM7<1,"",SUM($AG$7:AG7))
- Select the cell where your original formula is
Go to Conditional Formatting
Select New rule
go to Use the formula to determine the cells to be formatted
Instead of B1, enter the cell in which your formula is
=ISTNV(B1) abc
that's how it should work...or not?
Nikolino
smherring4 , I can't replicate your issue. Can you upload a sample without any confidential information please?
That's since you have no blank cells. Formula returns empty string, thus the cell has some value. Return NA() instead (you may hide it with conditional formatting or use helper column), upsize and color markers to show separate points if to work with gap option.
=IF(A2<1,NA(),SUM(A2))
