Display data points that are less than the chart trendline
 

I have a standard chart with an x and y axis and a linear trend line.

Is it possible to have the chart to only display the values that are less
than the trend line?

Thank you in advance for your help.

Display data points that are less than the chart trendline
 

Hi,
Sure you can. Firstly, either hide your existing data points, or make them a
very pale grey colour.
Then, assume your x values are in A1:A10, and your y values are in B1:B10.
In say A12, calculate the slope of the trendline with =SLOPE(B1:B10,A1:A10)
and in say A13 calculate the intercept using =INTERCEPT(B1:B10,A1:A10)
In column C, calculate the corresponding y value for each x value from
column A, by entering in C1, = $A$12*A1+$A$13 and drag down.
Then in D1, enter =IF(C1B1,B1,NA()) and drag down. This will return either
a number or an #N/A error.
Add this last series to the chart, which should only plot the points with a
value. The #N/A errors wont be plotted.
Format the new series as you like, usually with the same marker as the
original, but a brighter colour than the pale grey of the original ones.

Dave


"Mally" wrote:

I have a standard chart with an x and y axis and a linear trend line.

Is it possible to have the chart to only display the values that are less
than the trend line?

Thank you in advance for your help.

Display data points that are less than the chart trendline
 

Hi Dave

That worked great.

Thank you very much for your help.

Mally

"Dave Curtis" wrote:

Hi,
Sure you can. Firstly, either hide your existing data points, or make them a
very pale grey colour.
Then, assume your x values are in A1:A10, and your y values are in B1:B10.
In say A12, calculate the slope of the trendline with =SLOPE(B1:B10,A1:A10)
and in say A13 calculate the intercept using =INTERCEPT(B1:B10,A1:A10)
In column C, calculate the corresponding y value for each x value from
column A, by entering in C1, = $A$12*A1+$A$13 and drag down.
Then in D1, enter =IF(C1B1,B1,NA()) and drag down. This will return either
a number or an #N/A error.
Add this last series to the chart, which should only plot the points with a
value. The #N/A errors wont be plotted.
Format the new series as you like, usually with the same marker as the
original, but a brighter colour than the pale grey of the original ones.

Dave


"Mally" wrote:

I have a standard chart with an x and y axis and a linear trend line.

Is it possible to have the chart to only display the values that are less
than the trend line?

Thank you in advance for your help.