Dynamic chart ranges (slight variation)
 

I've learnt a lot from the articles on dynamic chart ranges using named
ranges and offsets. My problem is a little different, in 2 parts:
a) My spreadsheet shows data for a month, with a line for each day. I want
the graph to always show dates 1-31, (IE X values constant) and display 3
ranges of data for each day. Each day's data is automatically filled by lnks
to other spreadsheets received daily from external suppliers, but the cells
contain formulas, which return a "" or 0 if no data is present for any day.
Theses are shown as zeros on the graph. The counta still shows the cells as
zeros because of the presence of the formulas as they are not empty. As I
dont have a dynamic X value named range, Im not up to defining a dynamic Y
value range - please can someione advise?
b) second problem is that I also show a trendline based on data MTD, and in
options project forward to the end of the month. I manually cahnge this every
day - can I alter the formula for the trendline option to extend forwards by
the relevant number of days needed to project to the end of the month?
Please keep the answer simple - Im not an advanced user, just a keen
learner. Many thanks

Dynamic chart ranges (slight variation)
 

1. Change the formulas so they return NA() instead of 0 or "" for a blank
2. Calculate the trendline parameters in the worksheet using SLOPE() and
INTERCEPT(), and draw your line as a new XY series from X=0 to X=31.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Mi Manager" <Mi wrote in message
...
I've learnt a lot from the articles on dynamic chart ranges using named
ranges and offsets. My problem is a little different, in 2 parts:
a) My spreadsheet shows data for a month, with a line for each day. I want
the graph to always show dates 1-31, (IE X values constant) and display 3
ranges of data for each day. Each day's data is automatically filled by
lnks
to other spreadsheets received daily from external suppliers, but the
cells
contain formulas, which return a "" or 0 if no data is present for any
day.
Theses are shown as zeros on the graph. The counta still shows the cells
as
zeros because of the presence of the formulas as they are not empty. As I
dont have a dynamic X value named range, Im not up to defining a dynamic Y
value range - please can someione advise?
b) second problem is that I also show a trendline based on data MTD, and
in
options project forward to the end of the month. I manually cahnge this
every
day - can I alter the formula for the trendline option to extend forwards
by
the relevant number of days needed to project to the end of the month?
Please keep the answer simple - Im not an advanced user, just a keen
learner. Many thanks

Dynamic chart ranges (slight variation)
 

Thank you so much Jon- solution turned out to be simple in the end - but I
have learnt a lot along the way. Thank you for sharing your knowledge - very
grateful.

"Jon Peltier" wrote:

1. Change the formulas so they return NA() instead of 0 or "" for a blank
2. Calculate the trendline parameters in the worksheet using SLOPE() and
INTERCEPT(), and draw your line as a new XY series from X=0 to X=31.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Mi Manager" <Mi wrote in message
...
I've learnt a lot from the articles on dynamic chart ranges using named
ranges and offsets. My problem is a little different, in 2 parts:
a) My spreadsheet shows data for a month, with a line for each day. I want
the graph to always show dates 1-31, (IE X values constant) and display 3
ranges of data for each day. Each day's data is automatically filled by
lnks
to other spreadsheets received daily from external suppliers, but the
cells
contain formulas, which return a "" or 0 if no data is present for any
day.
Theses are shown as zeros on the graph. The counta still shows the cells
as
zeros because of the presence of the formulas as they are not empty. As I
dont have a dynamic X value named range, Im not up to defining a dynamic Y
value range - please can someione advise?
b) second problem is that I also show a trendline based on data MTD, and
in
options project forward to the end of the month. I manually cahnge this
every
day - can I alter the formula for the trendline option to extend forwards
by
the relevant number of days needed to project to the end of the month?
Please keep the answer simple - Im not an advanced user, just a keen
learner. Many thanks