Graph questions

[glenlee]

I have a bar graph that shows revenue by week, for week 1 thru week
52. Each week the graph pulls in the current week results. Is there
a way to have the graph only show results for week 1 thru the current
week, without me having to adjust the data source range each week.

Also, I have a bar graph that shows year-to-date revenue that is
updated each week. Is there a way that the scale can be automatically
updated to fit the data, without me having to manually adjust each
week.

 

[Jon Peltier]

1. Use dynamic ranges to populate the chart series:

http://peltiertech.com/Excel/Charts/Dynamics.html

2. Set the axis scale parameters to Auto (double click the axis, click the
Scale tab, check all the Auto boxes).

- Jon

 

[glenlee]

Thanks for the input. Dynamic Ranges work great when the source data
cells contain values. But I can't get it to work when the source data
cells contain formulas that equal -0-. Those weeks appear in my graph
with no results. I think using the Counta function, counts those
weeks even though there is no data for them.

 

[Jon Peltier]

COUNTA counts non-blank cells; note that a cell with a formula that returns
"" is not blank, because it contains a formula. COUNT counts cells with
numerical values.

If you use formulas that return zero when a linked cell is blank, you should
change the formula to return something else:

=IF(LEN(A1)>0,A1,"")
looks nice, looks like a blank, plotted as zero

=IF(LEN(A1)>0,A1,"blank")
descriptive, plotted as zero

=IF(LEN(A1)>0,A1,NA())
ugly #N/A error (hide with conditional formatting), not plotted in a line or
XY chart

- Jon