• 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.

Ignore blank values on a Trendline plotted on a Line Graph

Mukesh4478

New Member
Hello,


I really need some help here. I am trying to prepare a Line graph and need to show the Upper Natural Limit, Lower Natural Limit, Mean and the Data points. The data that goes into the Trend line is from a Range (say, A:Z) but this range contains values as well as there are cells which are blank. And I am planning to insert a User Form into this chart into this graph, as my raw data is huge and i need to make this dynamic.


Because of the Blank cells my graph is not making sense.


Can someone tell me how do i plot the Trendline in a way where it ignores/does not plot the blanks and trend line gets plotter only based on cells that have values.


Thank you

Mukesh
 
For the cells that contain constants (not formulas), you can have you chart ignore the blanks by selecting the chart, then going to Tools - Options - Chart, interpolate between blank cells.


For the cells that contain formulas, if you have a blank that you need to skip, rather than doing something like this:

=IF(SomeCondition,"",Value)


Do this:

=IF(SomeCondition,NA(),Value)


This will generate the #N/A error, which your chart will ignore.
 
Hi everyone,

I have encountered the similar problem and this =IF(SomeCondition,NA(),Value) doesn't seem to be fixing the issue.

Could it be because I am using excel 2013? Please find the attached file for working example and data set used.

Thanks for your time.

Kind Regards,
Z.
 

Attachments

  • Trendline issue - Copy.xlsx
    16.4 KB · Views: 11
Hi Narayan,

Thanks for getting back on this. This looks great but there's one issue.

When I try to add second series to display 2 different trendlines using the same principle as the above it doesn't work.

Thanks for your time.

Kind Regards,

Z.
 
Back
Top