Ignore blank values on a Trendline plotted on a Line Graph

[Mukesh4478]

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

 

[Mike86]

Put #N/A in the blank cells. That will allow Excel to ignore the blanks.

 

[Luke M]

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.

 

[mrzoogle]

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.

 

[NARAYANK991]

Hi ,

Can you see if this helps ?

http://www.andypope.info/ngs/ng31.htm

http://www.pcreview.co.uk/forums/trendlines-ignore-empty-cells-t1789371.html

Narayan

 

[mrzoogle]

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.