Ignoring zero values on graph

[FortuneSyn]

Hi!

I have a line graph that charts certain data per day in a month.

During the weekend there is no data. The graph however considers the number as zero, and I want it to just "skip" that day and plot the line directly from the friday date to the monday, instead of the line touching zero on saturday's and sundays.

Is it possible to do this? Or am I forced to adapt my table to only include days of the week?

Thankyou for your time.

[Andy Pope]

If you have cells for the weekend dates then they either need to be empty or contain NA() in order to surpress the data marker.

If still stuck post example file

[FortuneSyn]

I am using a portuguese version of excel

Do you know what I have to type into the cell in order to for it to be NA() and hence suppress the data?

[Andy Pope]

Afraid not.

What if you use VBA to populate a cell, try this in the immediate window. (ALT+F11 CTRL+G)

Activecell.formula = "=NA()"

then check the cells formula.

[mworonuk]

I think I have a similar question, maybe it could help us both.

I use a formula to populate column B under the condition column A is greater than 0. I want column B to not display 0 or - or #NA so I use this:

Please Login or Register  to view this content.

I later chart this column for multiple sheets. The really odd thing is that sometimes the charts don't drop to zero in the 'Else if' situation, but sometimes they do....

Is there an alternative to "" and NA() that effectively makes a cell NILL without displaying anything?

Maybe I could make a custom formula as a macro say called "Nill()" and when I put that into a cell it makes it blank? I don't know how this could work though since I still want to keep the IF() statement there.

[Andy Pope]

Your formula would need to be

=IF(A1>0,A1*3,NA())

Again, the use of #N/A in a line chart would be to remove the data marker only. It line not break the line although it will stop it from being treated as zero.

There is nothing else that a chart would understand in terms of non empty cells.
Numbers as Numbers
Text as Zero
N/A as marker surpress

If you think you really do have examples of where your original formula plots zeros or not then post them please.

One thing to check is that the chart type is line and not stacked line, as stacked line will always treat as zero.

[6StringJazzer]

There is nothing else that a chart would understand.
Numbers as Numbers
Text as Zero
N/A as marker surpress

If I may, I would like to add:
Empty cell as discontinuity (i.e., no marker and no line, for line graphs). (This is a true empty cell; a cell with blank text is treated as zero.)

[Andy Pope]

@6StringJazzer, whilst true the list was in relation to non empty cells.

I have added that cavet to my post

[FortuneSyn]

Thanks alot for your help Andy!

NA() is N鬃.DISP() in portuguese excel.

For other translations, this is a useful link:
http://dolf.trieschnigg.nl/excel/excel.html