Display data points that are less than the chart trendline

[Mally]

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.

 

[Dave Curtis]

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(C1>B1,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]

Hi Dave

That worked great.

Thank you very much for your help.

Mally