Excel 2010 Chart Trend Line Question

rockyw

Well-known Member
Joined
Dec 26, 2010
Messages
1,196
Office Version
  1. 2010
Platform
  1. Windows
I have a chart with each week of the year for 2015. As the weeks fill with data I would like to have a trend line based on what is there already, how can I get Excel to ignore weeks that have not accrued yet? Even a few weeks left in this year makes a trend line look bad. Thanks
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
You could make a dynamic chart that only included values that were present. This uses a defined name that increases in length as the amount of data increases. See Dynamic Charts for an example.

Or you could change the formula you're using. I presume the problem is you have data in a range, and you use a formula in the chart source data range that fetches the data. It either is a simple lookup like =A2, which returns zero if A2 is blank, or it is an attempt to suppress zero values like =IF(LEN(A2)=0,"",A2), which looks like a blank if A2 is blank. This isn't a real blank, though; it's text, and text is interpreted as zero in a chart. This results in data points along the X axis.

Use this formula instead:
=IF(LEN(A2)=0,NA(),A2)

This returns #N/A in the cell if A2 is blank. Looks ugly, but it isn't plotted, and a trendline in the chart will not include it.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,522
Messages
6,114,112
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top