Managing the Presentation of Zero values in Calculations in Charts
 

Issue: I need to manage the end of series data presentation when the x-axis
dates exceed the current data set, anticipating additional data over time.
The values are calculated and having the formula present before the data is
available returnes a #DIV/0 error.

Current State:
I have a version of spreadsheet that automatically appears to copy down the
formula when data is entered. Otherwise the cells are clear, protection is
off, hidden values is off, there appears to be no macros present.

Question:
How was the spreadsheet set up to accomplish this?
I can send a copy to anyone who want to tackle this question.

Managing the Presentation of Zero values in Calculations in Charts
 

If you're using Excel 2003, and the data range is defined as a List (Data
menu), it will update as data is added.

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


"RDrensek" wrote in message
...
Issue: I need to manage the end of series data presentation when the
x-axis
dates exceed the current data set, anticipating additional data over time.
The values are calculated and having the formula present before the data
is
available returnes a #DIV/0 error.

Current State:
I have a version of spreadsheet that automatically appears to copy down
the
formula when data is entered. Otherwise the cells are clear, protection
is
off, hidden values is off, there appears to be no macros present.

Question:
How was the spreadsheet set up to accomplish this?
I can send a copy to anyone who want to tackle this question.

Managing the Presentation of Zero values in Calculations in Ch
 

Thank you for the reply.

I've checked by click on a cell in the data rane and checked the list menu.
There appears to be no list set up.

What is observed is: The chart series is referenced to the calculated cells.
Below the last data row are blank cells (except for a goal field [set
at93.4%]). When data is entered into the data columns, the series referenced
column generates a formula
(=Q42/P42) and calculates the result. That result then appears in the
chart.

The chart then avoids the drop to zero at the end of the series. The x-axis
is for the year and the data doesn't run that far out. If the referenced
column contained the formula (@div/0, error when no data present), the chart
series would descend to the x-axis.

Any other suggestions?



"Jon Peltier" wrote:

If you're using Excel 2003, and the data range is defined as a List (Data
menu), it will update as data is added.

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


"RDrensek" wrote in message
...
Issue: I need to manage the end of series data presentation when the
x-axis
dates exceed the current data set, anticipating additional data over time.
The values are calculated and having the formula present before the data
is
available returnes a #DIV/0 error.

Current State:
I have a version of spreadsheet that automatically appears to copy down
the
formula when data is entered. Otherwise the cells are clear, protection
is
off, hidden values is off, there appears to be no macros present.

Question:
How was the spreadsheet set up to accomplish this?
I can send a copy to anyone who want to tackle this question.

Managing the Presentation of Zero values in Calculations in Ch
 

I checked this behavior out. Three things are necessary for this kind of
autofill.

1. The data must be a well defined list (though not an official Excel 2003
"List"), with headers in the first row, one field per column, no blank rows
or columns.
2. The formulas must initially be to the right of precedent cells.
3. Tools menu Options Edit tab, "Extend Data Range Formats and Formulas"
must be checked.

After it is set up and the formulas update, you can rearrange columns
(undoing requirement 2).

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


"RDrensek" wrote in message
...
Thank you for the reply.

I've checked by click on a cell in the data rane and checked the list
menu.
There appears to be no list set up.

What is observed is: The chart series is referenced to the calculated
cells.
Below the last data row are blank cells (except for a goal field [set
at93.4%]). When data is entered into the data columns, the series
referenced
column generates a formula
(=Q42/P42) and calculates the result. That result then appears in the
chart.

The chart then avoids the drop to zero at the end of the series. The
x-axis
is for the year and the data doesn't run that far out. If the referenced
column contained the formula (@div/0, error when no data present), the
chart
series would descend to the x-axis.

Any other suggestions?



"Jon Peltier" wrote:

If you're using Excel 2003, and the data range is defined as a List (Data
menu), it will update as data is added.

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


"RDrensek" wrote in message
...
Issue: I need to manage the end of series data presentation when the
x-axis
dates exceed the current data set, anticipating additional data over
time.
The values are calculated and having the formula present before the
data
is
available returnes a #DIV/0 error.

Current State:
I have a version of spreadsheet that automatically appears to copy down
the
formula when data is entered. Otherwise the cells are clear,
protection
is
off, hidden values is off, there appears to be no macros present.

Question:
How was the spreadsheet set up to accomplish this?
I can send a copy to anyone who want to tackle this question.

Managing the Presentation of Zero values in Calculations in Ch
 

Thanks for your help. This is great and a neat way to have clean series data
presented in Excel charts. A little tricky to set up, but not hard.

"Jon Peltier" wrote:

I checked this behavior out. Three things are necessary for this kind of
autofill.

1. The data must be a well defined list (though not an official Excel 2003
"List"), with headers in the first row, one field per column, no blank rows
or columns.
2. The formulas must initially be to the right of precedent cells.
3. Tools menu Options Edit tab, "Extend Data Range Formats and Formulas"
must be checked.

After it is set up and the formulas update, you can rearrange columns
(undoing requirement 2).

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


"RDrensek" wrote in message
...
Thank you for the reply.

I've checked by click on a cell in the data rane and checked the list
menu.
There appears to be no list set up.

What is observed is: The chart series is referenced to the calculated
cells.
Below the last data row are blank cells (except for a goal field [set
at93.4%]). When data is entered into the data columns, the series
referenced
column generates a formula
(=Q42/P42) and calculates the result. That result then appears in the
chart.

The chart then avoids the drop to zero at the end of the series. The
x-axis
is for the year and the data doesn't run that far out. If the referenced
column contained the formula (@div/0, error when no data present), the
chart
series would descend to the x-axis.

Any other suggestions?



"Jon Peltier" wrote:

If you're using Excel 2003, and the data range is defined as a List (Data
menu), it will update as data is added.

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


"RDrensek" wrote in message
...
Issue: I need to manage the end of series data presentation when the
x-axis
dates exceed the current data set, anticipating additional data over
time.
The values are calculated and having the formula present before the
data
is
available returnes a #DIV/0 error.

Current State:
I have a version of spreadsheet that automatically appears to copy down
the
formula when data is entered. Otherwise the cells are clear,
protection
is
off, hidden values is off, there appears to be no macros present.

Question:
How was the spreadsheet set up to accomplish this?
I can send a copy to anyone who want to tackle this question.

Managing the Presentation of Zero values in Calculations in Ch
 

I experimented with this a little further and the autofill feature requires 4
rows of data to establish the "list". After that, copying down using the
corner tab in the cell will initiate teh autofill.

"RDrensek" wrote:

Thanks for your help. This is great and a neat way to have clean series data
presented in Excel charts. A little tricky to set up, but not hard.

"Jon Peltier" wrote:

I checked this behavior out. Three things are necessary for this kind of
autofill.

1. The data must be a well defined list (though not an official Excel 2003
"List"), with headers in the first row, one field per column, no blank rows
or columns.
2. The formulas must initially be to the right of precedent cells.
3. Tools menu Options Edit tab, "Extend Data Range Formats and Formulas"
must be checked.

After it is set up and the formulas update, you can rearrange columns
(undoing requirement 2).

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


"RDrensek" wrote in message
...
Thank you for the reply.

I've checked by click on a cell in the data rane and checked the list
menu.
There appears to be no list set up.

What is observed is: The chart series is referenced to the calculated
cells.
Below the last data row are blank cells (except for a goal field [set
at93.4%]). When data is entered into the data columns, the series
referenced
column generates a formula
(=Q42/P42) and calculates the result. That result then appears in the
chart.

The chart then avoids the drop to zero at the end of the series. The
x-axis
is for the year and the data doesn't run that far out. If the referenced
column contained the formula (@div/0, error when no data present), the
chart
series would descend to the x-axis.

Any other suggestions?



"Jon Peltier" wrote:

If you're using Excel 2003, and the data range is defined as a List (Data
menu), it will update as data is added.

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


"RDrensek" wrote in message
...
Issue: I need to manage the end of series data presentation when the
x-axis
dates exceed the current data set, anticipating additional data over
time.
The values are calculated and having the formula present before the
data
is
available returnes a #DIV/0 error.

Current State:
I have a version of spreadsheet that automatically appears to copy down
the
formula when data is entered. Otherwise the cells are clear,
protection
is
off, hidden values is off, there appears to be no macros present.

Question:
How was the spreadsheet set up to accomplish this?
I can send a copy to anyone who want to tackle this question.