Blank cells plotted as ZERO on chart

Hello

Welcome to the club.
This question was asked many times.

The behaviour is perfectly normal.

For the chart builder, an empty cell has the value 0.

To get rid of that, it would be necessary to create an auxiliary column in which a formula will replace the blank cells by interpolated values.



I have no spare time to build a general formula to fill the auxiliary column.

Yvan KOENIG (from FRANCE vendredi 25 janvier 2008 20:56:29)

I could not find a way to do this. I can only suggest two (pretty poor) work-arounds:

(1) Use a column chart instead. Of course, the visual effect is very different from a line chart. But at least it does not report non-existent data as data with a value of zero. At least, not quite so brazenly.

(2) Dig up an old copy of AppleWorks. It suffered from this same problem, but it did give you the option of copying the graph and pasting it back in as an editable picture. The picture was no longer linked to your data, but you could edit it - for example, you could delete the line segments connected to the 'zero' point

...and of course there's always (3) Use Excel or OpenOffice or some other program that gives you the option of skipping missing data, rather than plotting it as though it were zero.

And most importantly, send your wishes for future feature additions to Apple thru Numbers/Provide Numbers Feedback. Don't post any such requests here, as Apple employees don't officially monitor this board, and other posters will whine at you endlessly about it if you do.

Hi Photochromatic,
Welcome to Numbers discussions.

Please give this a go in a column: =IF(ISBLANK(B)," ",C*B)
Copy down then chart off that data.

Let us know if this works for you.

Sincerely,
RicD

this might help...

I've noticed that if you create a line chart from a range of cells including a cell that is blank (or FALSE or "" or anything other than a number, as far as I can tell), the data will be plotted as though that cell had a zero in it, as you originally noticed.

But, if you fill the cell with something (like the number zero, or another number, or anything , really), then create the chart, THEN delete the contents of that cell, the corresponding points and line segments on the chart will disappear.

It seems that you have to actually delete the cell contents for this to happen. Simply having a formula that results in "" or zero will not do it.

JWReed,
You're correct, the cell needs to be completely empty for it not to plot.

Wow I goofed, the =IF(ISBLANK(B)," ",C*B) doesn't work as I had thought. I tested it on a long string of data, however, my interpretation of my own chart was incorrect.

Ouch! Ole-timers set in, huh. Please excuse my error, today I didn't take my Metimucel and Geritol.

Sincerely,

RicD

Photochromatic,

Here's something to consider that's a little different from suggestions already offered. It involves creating a table from the original that eliminates zero and blank columns.

We begin with the original table (Sales) and add another row which counts the number of non-zero columns. Then, a second table (Sales - Skip Dates) is created. Note that both tables do not use headers and other than row headers, the new table is calculated from the original.

Select the non-zero cells of this new table and it becomes the basis for the chart. Sample formulas from each row are shown.



I suspect someone will find more efficient formulas, but don't hesitate to ask questions if interested.

pw

Hello

On a pure calculation point of vue this process is perfect.
Alas, it gives a reprentation of the datas which is meaningless.
No one may perceive that some days are missing.
The horizontal gap between point don't match the time gap.

This is why I choose to work with interpolated values which, from my point of vue, give a better description of the analyzed evolution.

Yvan KOENIG (from FRANCE jeudi 31 janvier 2008 11:28:38)

Good morning, Yvan,
(It's morning in the Chicago area)

I agree, this solution could very well be meaningless. And since you bring this up, the concept of interpolating a value may not be appropriate either. We all may be on the wrong track. The thing is we do not know what the original poster is attempting to plot nor do we know why those dates are blank. All we can do is suggest a solution to a problem as the OP has posed it and we have interpreted it. The OP must decide if any of the responses are suitable to his/her situation.

Actually, as a reader, the neat thing about this forum is to see different approaches to a problem and to see various applications or uses of functions leading to solutions - a great learning experience.

Best wishes,
PW

Message was edited by: pw1840

Don't worry

I was just trying, with my poor English, to point the fact that skipping some dates may give a wrong aspect to the evolution described by the chart.


Of course, if want to chart the amount of products sold everyday, interpolating would be sad but for this kind os discontinuous datas, I'm not sure that a line plotting (curve) is the best soluce.

Points or bars would be better.

With other words, I was not saying "you made an error" but "caution, this way to display things may be inappropriate".

Yvan KOENIG (from FRANCE jeudi 31 janvier 2008 21:23:11)

Hey, this worked for me. I was trying to plot a percentage from a column only if it had data in it. The entire percentage column showed up as $0.00 until I tried your fix, now it is blank if there is no data in the previous column! Here's what I used: =IF(ISBLANK(Gross)," ",E2*70%). Thanks so much!