Is there a way to control the trend line used against a bar chart?
I have a bar chart that shows a count of hours per month and trend line for same.
For instance in 2016 we have as of August 1 gone through 7 month and 1 day as of today (8/1/16). My bar chart shows hours on each months column and a trend line through August. August though brings the trend line down because of the low count of hours as of 8/1. This skews the trend.
I only want the trend line to cover through the end of July but not incomplete months like August, although I do want the count shown on the bar chart for the 1 (or more days) in August.
Does anyone know of a work around to this situation?
Set your range for the bar chart to end in August, but the range for your trend line to end in July. Then “project” your trend line for an extra month so that you get a data point for the line.
I also sometimes give the user an toggle button to “include current month” where the graph is showing monthly totals - stops the graph auto-scaling to accommodate a small number at the start of a month when the monthly total is in the 10k.
Add logic in the spreadsheet to include values in your trend data array if after a specific date. Like for the cell for August trend you would only want populated after 31 August. So setup a row with cells for end of month dates for Jan-Dec …say (A1-L1).
Then use = If Today() > H1 Then (cell reference to Aug data) Else “” in your data array for Aug and = If Today() > I1 Then (cell reference to Sep data) Else “” for Sep… etc… in your trend line data array/series for your combination chart. Show data if current date exceeds target date.
Then in your chart behavior tab under “Ignore Blank Cells”, set the checkbox “in values”.
The formulas will keep the cells blank until the correct date and the behavior settings will keep the data points and values off the chart.
Leave the bar chart data array without formulas and the chart bar will grow throughout the month.
(BOB member since 2009-11-19)
(BOB member since 2005-03-01)