How to make chart ignore "zero"/FALSE values

**BCB** · 03-08-2007, 11:42 PM

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** · 03-09-2007, 04:22 AM

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** · 03-09-2007, 10:03 PM

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** · 03-10-2007, 04:56 AM

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** · 03-11-2007, 10:40 PM

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** · 03-12-2007, 05:56 PM

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** · 03-13-2007, 06:42 AM

Originally Posted by 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

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** · 03-15-2007, 06:59 PM

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** · 03-16-2007, 02:41 AM

Originally Posted by 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

what columns / rows are occupied by your data?

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

---

**BCB** · 03-18-2007, 03:02 PM

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** · 03-19-2007, 04:04 AM

Originally Posted by 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

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** · 03-20-2007, 03:38 PM

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

Thanks a lot.
BCB

**Bryan Hessey** · 03-20-2007, 09:37 PM

Originally Posted by BCB

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** · 03-23-2007, 04:27 PM

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** · 03-23-2007, 05:57 PM

Originally Posted by 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

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.

---