Ignoring NA values in LineChart trendlines?

[groitblat]

I've been tasked to create a linear chart that shows both the linear data as well as the polynomial data for testing results that can vary from 30 minutes to 90 minutes of how individuals burn calories.

I've gotten the linear piece sort of handled using the NA() statement so that the lines don't extend past where the data goes (See tab metobolic testing zones) but when I put the polynomial trendline on the same chart the NA data is plotted and chaos ensues. (See tab with trendline)

Is there a way to use a polynomial trendline and have it ignore the na data? OR do I need to figure out how to make this dynamic in some way using the horizontal data points?
I know I can change the range but being that ultimately this form will be in the hands of non-excel guru's want to make it as simple as possible where they cut & paste in data & the rest of the data sheets populate from there.

Any help/suggestions would be greatly appreciated.

- gina

[MrShorty]

My first observation is that heart rate is a real number -- not a category. Wouldn't you want a scatter plot rather than a line plot for these? I also think your issue with the N/A's would resolve itself if you changed the chart to a scatter plot (line no marker). Your polynomial trendline will also have more meaning, since the x value in the regression will be heart rate and not some other "count number" that Excel uses in line charts.

[groitblat]

The data most relevant is the intersection of the Fat & CHO calories percentages which are based on the average for the last 2 minutes of
every 5 minute interval.
HR as the axis helps client identify where most efficient training zone resides.....

I've played around with the xy scatter as well but before I used the na() formula so will review that option as well