# Microsoft Office Application Help - Excel Help forum > Excel Charting & Pivots >  >  How to make chart ignore "zero"/FALSE values

## BCB

Hello

I'm trying to make a chart where, among other regular values, there's supposed to be a column or two with some IF-formulas, which returns some values or otherwise nothing. 
The "nothing" bit has given me some problems, as the zero returned reads like a very low value indeed, with the chart line dropping accordingly. I've considered making it return the same value as the former cell, but that's not very pretty either. Does anyone know how to make it return something that the chart also perceives as absolutely nothing?
Appreciate any help
BCB

----------


## Carim

Hi,

Below is the solution proposed by Jon Peltier ...
If there are true zeros in the data, perhaps the easiest way to exclude them from 
the chart is to use an autofilter on the data that hides the rows with zeros. 
Alternatively, you could insert a column to hide the zeros. Say the range with zeros 
is in B1:B10. Select C1:C10, and enter this formula into C1: 



```
Please Login or Register  to view this content.
```


Hold CTRL while pressing Enter, which enters the formula into all selected cells. 
This puts ugly #N/A errors into the sheet, but the chart doesn't plot the points. 
Hide the errors with conditional formatting, as Debra Dalgleish shows here: 
http://contextures.com/xlCondFormat03.html#Errors

----------


## BCB

Thanks for the tip, but the NA solution is being treated by the chart just like the ones I mentioned: it does plot the point. It seems like it treats anything in the cell that isn't a value like a zero.
The ugly results you mentioned would be welcome in my sheet, as my problem is more the opposite: neat looking columns with the required values or otherwise nothing visible on the sheet - until I click the individual cell and reveal the formula, which apparently is being read by the chart all the way.

Just in case it matters:
The formula I'm currently working with is not exactly returning "TRUE/FALSE" or "1/0" as such, but is derived from one of those (which, as far as I recall, also returned the same mess when charted).
It goes like this
=IF(LARGE(B62:B71)<=(LARGE(B72:B81);(LARGE(B72:B81);"")
Maybe it's my Excel edition (2003) that's too old.

Thanks anyway.
BCB

----------


## Carim

Hi,

As Bryan has pointed out in a previous post, the final outcome depends on the chart type :
1. For Line charts the Tools, Options, Charts, 'Interpolated' will continue the line from the point before to the point after, ignoring the missing item.
2. For scatter charts and bar charts a gap is left.

----------


## recklaw

Hey guys,

I am having a similar charting problem.
I have potentially 23 inputs, although most likely there will only be 4 or 5.
I am trying to get my chart only the legend and data for the whole series where the data is greater than 0.

I initially had the full series shown and then used 2 colums to effectively make the cell blank if the data = 0 or to input the cell if the data was greater than 0 i.e. =if(A1="","",A1).

This produced a list which only visually shows data for positive cells, however when I enter into the source data, it picks up all the 0 data as well.

----------


## BCB

Hello

Well, there's no way around deleting them manually, it seems. Not that big a problem, actually; just the urge to automate any function, with further copying in mind.
 Thanks anyway.

Recklaw
Yes, it appears to read the blank cells as well, as long as they are blank as a result of a formula.

Getting curious about the 2007 edition. 

BCB

----------


## Bryan Hessey

> Hello
> 
> Well, there's no way around deleting them manually, it seems. Not that big a problem, actually; just the urge to automate any function, with further copying in mind.
>  Thanks anyway.
> 
> Recklaw
> Yes, it appears to read the blank cells as well, as long as they are blank as a result of a formula.
> 
> Getting curious about the 2007 edition. 
> ...



HI,

If your chart is a column chart, and you really need to avoid the #N/A then a small macro (or Change event trigger) to Hide the #N/A columns might be what you need, the chart can be set to Show Visible Only.

hth
---

----------


## BCB

Hi

Oh, I'm afraid the macro business (however small) is currently in the mañana dept -until the new edition is installed, or at least  until further skills are acquired.
Thanks anyway for the tip(s) - will look it up when the mentioned time comes. 

BCB

----------


## Bryan Hessey

> Hi
> 
> Oh, I'm afraid the macro business (however small) is currently in the mañana dept -until the new edition is installed, or at least  until further skills are acquired.
> Thanks anyway for the tip(s) - will look it up when the mentioned time comes. 
> 
> BCB



what columns / rows are occupied by your data?

and which would be displayed always (ie, B5:AZ5 - always show B5:H5)

---

----------


## BCB

Hi

First of all I'd like to point out - to avoid any misunderstanding - that the chart in question is a line chart. I just realised that my initial post might be interpreted as dealing with a column chart - which is not the case. The mentioned columns are the ones containing the numbers for the lines.

To your question: for this particular problem just two columns would do. Column A has some values for which the (utterly rudimental) formulae in Col. B are meant to find the lowest value among the last 10.
Something like this:
=IF(MIN(A11:A20)<=MIN(A5:A15);MIN(A11:A20);"")
Yes, I know, it's not even wrong, but the idea was to stuff it with enough conditions (differences, percentages...) to finally point out significant lows - all by itself, when copied downwards.
Any quick solution for _that_ problem would of course be greatly appreciated as well.
But when I see how my charts treat the "" cells I'm tempted to let it go for the time being.
PS:
Tried the Tools..-.. Interpolation thing Carim suggested, but no difference.

Thanks for your efforts, anyway
BCB

----------


## Bryan Hessey

> Hi
> 
> First of all I'd like to point out - to avoid any misunderstanding - that the chart in question is a line chart. I just realised that my initial post might be interpreted as dealing with a column chart - which is not the case. The mentioned columns are the ones containing the numbers for the lines.
> 
> To your question: for this particular problem just two columns would do. Column A has some values for which the (utterly rudimental) formulae in Col. B are meant to find the lowest value among the last 10.
> Something like this:
> =IF(MIN(A11:A20)<=MIN(A5:A15);MIN(A11:A20);"")
> Yes, I know, it's not even wrong, but the idea was to stuff it with enough conditions (differences, percentages...) to finally point out significant lows - all by itself, when copied downwards.
> Any quick solution for _that_ problem would of course be greatly appreciated as well.
> ...



Hi,

the suggestion of 'Interpolation' said 'Line' rather than 'Column'.

To remove the blanks, you can hide blank rows, the macro 


```
Please Login or Register  to view this content.
```


 should do that. To unhide just use the normal rows select and unhide.

To store the code, Tools, Macro, Macros, select a name and Create, then copy the code (excluding the name row) into there.
Close the VB Editor, and, if you want to use a Shortcut Key=, Tools, Macro, Macros, select the macro and Options.

With the chart selected, Tools, Chart and tick Plot Visible Only.

hth
---

----------


## BCB

Phew, that's quite a load..
- and literally uncharted territory. Copied it, must learn some of this someday.

Thanks a lot.
BCB

----------


## Bryan Hessey

> Phew, that's quite a load..
> - and literally uncharted territory. Copied it, must learn some of this someday.
> 
> Thanks a lot.
> BCB



Hi,

just read the bits you know already, the rest probably doesn't matter, ie

 If Cells value = ""

 If Cells value = "" or Cells value = 0

etc

--

did it work for what you wanted?
---

----------


## BCB

Thanks for your enthusiasm on my behalf, but I'm afraid the past tense in your question is more than a bit premature, as my macros are disabled due to some Acrobat reader issue, and, apart from that, I already have a little translation hassle as it is with the regular formulae you've already helped me with here. 
Given that, I wouldn't even try to go into the bureaucracy of trying to translate & convert the expressions & signs in a macro recording at present.
But, as already mentioned, I've copied your suggestions and a few others' with future apprehension (on a spanking new Excel) in mind. Appreciate your help, as always, and will hopefully answer your last question - not too many years from now...

BCB

----------


## Bryan Hessey

> Thanks for your enthusiasm on my behalf, but I'm afraid the past tense in your question is more than a bit premature, as my macros are disabled due to some Acrobat reader issue, and, apart from that, I already have a little translation hassle as it is with the regular formulae you've already helped me with here. 
> Given that, I wouldn't even try to go into the bureaucracy of trying to translate & convert the expressions & signs in a macro recording at present.
> But, as already mentioned, I've copied your suggestions and a few others' with future apprehension (on a spanking new Excel) in mind. Appreciate your help, as always, and will hopefully answer your last question - not too many years from now...
> 
> BCB



ok - good luck with a new version of Excel, the old Acrobat problem was annoying.

Just a note if you need to hide the rows 'by hand' is that you can use most of the Windows 'select' function, ie

select an item (in this case a row)
Shift-click another row to select all rows between, 
CTRL-click other rows to be added to the selection
rightmouse in the selection and Hide.

The one bit that doesn't work in Excel is the CTRL-click to UN-select, so choose wisely or you have to re-begin the selection.

---

----------


## BCB

Hi
It's copied; might come in handy.
Thanks again.
BCB

----------

