• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Don't show line graph when 0

M

mr_hiboy

Member
Hi,

I've a set of values that creates a line graph, most values are around the 90% mark, but there are a few zeros.

Rather the the line graph dropping from 90% to 0%, I'd rather not show the line to zero.

Tried everything (well everything i thought of), the only way it work is when i actually delete the formula, leaving a genuine blank cell. But this is practical, as I need the formula.

Thanks in advance for any help...

Paul
 
Hi hilboy

The trick is to have a formula like the following.

=IF(B4=0,NA(),B4)

Where B4 is your source data. Excel will not plot NA(). Will attach a file to show workings.

Alternatively you could group the columns if they are zero. Excel will not plot hidden columns/rows.

That is all I got.

Take care

Smallman
 

Attachments

  • Nozero.xlsm
    11.9 KB · Views: 90
Thanks for response.

Yeah tried that, it doesn't plot the point but it doesn't remove the line. So it skips the points and the line goes to the next point, which gives a false picture of the zero value.

Cheers
 
What are we talking about here, one line, two lines, three lines??? Did you try the second option? If you hide the columns that are NA then the Months disappear too. That way the data goes from Jan - April then picks up again in August and you can see those months are not there. What does this line actually mean, I responded on what I thought it meant but re reading it I am scratching my head.

Rather the the line graph dropping from 90% to 0%, I'd rather not show the line to zero
Click to expand...

ANyways if you upload a file you will get far better assistance. With over 100 posts this should be ingrained by now.

Take care

Smallman
 
Hi Paul ,

Let me understand your problem.

1. Your X-axis has the months , say from January through till December.

2. Your values for the months of say March , July and August are 0.

Can you specify what the graph should do for these 3 months ?

Should there be gaps in the line with the X-axis showing all the months , or should the X-axis rearrange itself so that these months are not displayed on the X-axis ?

Narayan
 
Paul

That is a very unusual way to look at graphing something. I think what you are looking for is not available but I could be wrong as I have not seen charting done like that. You could create a bespoke workaround quite easily which basically is a procedure that looks into your range of cells to be charted, if any cell value is zero then it deletes the cell. This could all happen automatically whenever 0 is entered in the charting range.

And yes let the group know if you need further assistance.

Smallman
 
You must log in or register to reply here.
Back
Top