| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.charting
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Return Matched Numeric Values across Rows | Excel Worksheet Functions | |||
| suppress zero values in line charts? | Charts and Charting in Excel | |||
| suppress zero values in line charts? | Charts and Charting in Excel | |||
| Newbie to charts question - projecting values between data points | Excel Discussion (Misc queries) | |||
| To get a lot of charts that contain different values in a minute | Charts and Charting in Excel | |||
Linear Mode
