Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I display ratios of X and Y points on an Excel chart? | Charts and Charting in Excel | |||
Lock Chart Display to last 14 days (or data points) | Charts and Charting in Excel | |||
Accessing values in points collection of chart & conditional display | Charts and Charting in Excel | |||
Trendline excluding last 2 data points of series | Charts and Charting in Excel | |||
Display negative data points differently. | Charts and Charting in Excel |