Trendlines - ignoring zero/blank values in the range

[jack0fspad3s]

Hi,

I’m creating graphs using exponential trendlines (=Growth formula), however sometimes in my dataset there are zero values in the range (these are formulas which return blank values so are not empty as such). This means that the trend line cannot be calculated as I get #Value errors.

If I delete the formulas, the trendlines appear on the graph which is how I know it is possible to calculate this.

I have looked into dynamic ranges which I think could be part of the solution, however my blanks can be in the middle of the data (and not at the end), so using this formula below doesn’t help:
=B2:INDEX(B2:B9, COUNTIF(B2:B9,”>0”)).

Any help appreciated!

 

[awoohaw]

if you build your formula to return NA(), these values are are not plotted.

 

[jack0fspad3s]

Thanks for the response.

This doesn’t work as the trendline (exponential) disappears due to there being zero or error values

 

[awoohaw]

Thanks for the response.

This doesn’t work as the trendline (exponential) disappears due to there being zero or error val

Confused. I hope someone else here can help.