Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Do not show blank cells as 0 in chart
I have a spreadsheet with a two columns, one for expected enrolment numbers
and the first column of actual enrolments. For April we expect 90, so have a blank cell to the right for actual. This is showing a line from the March enrolment of 3 down to 0. How do I get the line to stop at 3 until I put a figure in for April? |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Do not show blank cells as 0 in chart
Excel lets you choose how you can plot empty cells.
As zeros, not plotted or interpolated. To make your choice, select your chart, go to Tools Options Charts and select the Not Plotted option button for empty cells. ....Kelly "Sheila Innes" wrote in message ... I have a spreadsheet with a two columns, one for expected enrolment numbers and the first column of actual enrolments. For April we expect 90, so have a blank cell to the right for actual. This is showing a line from the March enrolment of 3 down to 0. How do I get the line to stop at 3 until I put a figure in for April? |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Do not show blank cells as 0 in chart
Thanks Kelly. Problem - the Not Plotted is grayed out, so I can't mark it.
Any ideas? "Kelly O'Day" wrote: Excel lets you choose how you can plot empty cells. As zeros, not plotted or interpolated. To make your choice, select your chart, go to Tools Options Charts and select the Not Plotted option button for empty cells. ....Kelly "Sheila Innes" wrote in message ... I have a spreadsheet with a two columns, one for expected enrolment numbers and the first column of actual enrolments. For April we expect 90, so have a blank cell to the right for actual. This is showing a line from the March enrolment of 3 down to 0. How do I get the line to stop at 3 until I put a figure in for April? |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
Do not show blank cells as 0 in chart
1. Select the chart before going to Tools - Options - Charts.
2. What kind of chart is it? 3. If it's a line or XY chart, if you have a formula that returns "", understand that this is not a blank cell, it's a cell with a formula, so Kelly's solution won't change how the cell plots. Change the "" in the formula to NA(), which gives you an ugly #N/A in the cell, but gives you the Interpolate behavior in the chart. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services - Tutorials and Custom Solutions - http://PeltierTech.com/ 2006 Excel User Conference, 19-21 April, Atlantic City, NJ http://peltiertech.com/Excel/ExcelUserConf06.html _______ "Sheila Innes" wrote in message ... Thanks Kelly. Problem - the Not Plotted is grayed out, so I can't mark it. Any ideas? "Kelly O'Day" wrote: Excel lets you choose how you can plot empty cells. As zeros, not plotted or interpolated. To make your choice, select your chart, go to Tools Options Charts and select the Not Plotted option button for empty cells. ....Kelly "Sheila Innes" wrote in message ... I have a spreadsheet with a two columns, one for expected enrolment numbers and the first column of actual enrolments. For April we expect 90, so have a blank cell to the right for actual. This is showing a line from the March enrolment of 3 down to 0. How do I get the line to stop at 3 until I put a figure in for April? |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
Do not show blank cells as 0 in chart
Hi Jon
I checked the chart and the 'not plotted' option is already selected. The equation for the chart is =Recruitment!$B$4:$B$9,Recruitment!$O$4:$P$9. The B selection is the month, the O is the actual and the P is the target. Any more help much appreciated. Regards Sheila "Jon Peltier" wrote: 1. Select the chart before going to Tools - Options - Charts. 2. What kind of chart is it? 3. If it's a line or XY chart, if you have a formula that returns "", understand that this is not a blank cell, it's a cell with a formula, so Kelly's solution won't change how the cell plots. Change the "" in the formula to NA(), which gives you an ugly #N/A in the cell, but gives you the Interpolate behavior in the chart. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services - Tutorials and Custom Solutions - http://PeltierTech.com/ 2006 Excel User Conference, 19-21 April, Atlantic City, NJ http://peltiertech.com/Excel/ExcelUserConf06.html _______ "Sheila Innes" wrote in message ... Thanks Kelly. Problem - the Not Plotted is grayed out, so I can't mark it. Any ideas? "Kelly O'Day" wrote: Excel lets you choose how you can plot empty cells. As zeros, not plotted or interpolated. To make your choice, select your chart, go to Tools Options Charts and select the Not Plotted option button for empty cells. ....Kelly "Sheila Innes" wrote in message ... I have a spreadsheet with a two columns, one for expected enrolment numbers and the first column of actual enrolments. For April we expect 90, so have a blank cell to the right for actual. This is showing a line from the March enrolment of 3 down to 0. How do I get the line to stop at 3 until I put a figure in for April? |
#6
Posted to microsoft.public.excel.charting
|
|||
|
|||
Do not show blank cells as 0 in chart
Sheila -
Do you have formulas in Recruitment!$B$4:$B$9 and Recruitment!$O$4:$P$9? This is where you need to change "" to NA(), if it's a line or XY chart. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services - Tutorials and Custom Solutions - http://PeltierTech.com/ 2006 Excel User Conference, 19-21 April, Atlantic City, NJ http://peltiertech.com/Excel/ExcelUserConf06.html _______ "Sheila Innes" wrote in message ... Hi Jon I checked the chart and the 'not plotted' option is already selected. The equation for the chart is =Recruitment!$B$4:$B$9,Recruitment!$O$4:$P$9. The B selection is the month, the O is the actual and the P is the target. Any more help much appreciated. Regards Sheila "Jon Peltier" wrote: 1. Select the chart before going to Tools - Options - Charts. 2. What kind of chart is it? 3. If it's a line or XY chart, if you have a formula that returns "", understand that this is not a blank cell, it's a cell with a formula, so Kelly's solution won't change how the cell plots. Change the "" in the formula to NA(), which gives you an ugly #N/A in the cell, but gives you the Interpolate behavior in the chart. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services - Tutorials and Custom Solutions - http://PeltierTech.com/ 2006 Excel User Conference, 19-21 April, Atlantic City, NJ http://peltiertech.com/Excel/ExcelUserConf06.html _______ "Sheila Innes" wrote in message ... Thanks Kelly. Problem - the Not Plotted is grayed out, so I can't mark it. Any ideas? "Kelly O'Day" wrote: Excel lets you choose how you can plot empty cells. As zeros, not plotted or interpolated. To make your choice, select your chart, go to Tools Options Charts and select the Not Plotted option button for empty cells. ....Kelly "Sheila Innes" wrote in message ... I have a spreadsheet with a two columns, one for expected enrolment numbers and the first column of actual enrolments. For April we expect 90, so have a blank cell to the right for actual. This is showing a line from the March enrolment of 3 down to 0. How do I get the line to stop at 3 until I put a figure in for April? |
#7
Posted to microsoft.public.excel.charting
|
|||
|
|||
Do not show blank cells as 0 in chart
Jon
No, there are no formulas in these cells. Sheila "Jon Peltier" wrote: Sheila - Do you have formulas in Recruitment!$B$4:$B$9 and Recruitment!$O$4:$P$9? This is where you need to change "" to NA(), if it's a line or XY chart. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services - Tutorials and Custom Solutions - http://PeltierTech.com/ 2006 Excel User Conference, 19-21 April, Atlantic City, NJ http://peltiertech.com/Excel/ExcelUserConf06.html _______ "Sheila Innes" wrote in message ... Hi Jon I checked the chart and the 'not plotted' option is already selected. The equation for the chart is =Recruitment!$B$4:$B$9,Recruitment!$O$4:$P$9. The B selection is the month, the O is the actual and the P is the target. Any more help much appreciated. Regards Sheila "Jon Peltier" wrote: 1. Select the chart before going to Tools - Options - Charts. 2. What kind of chart is it? 3. If it's a line or XY chart, if you have a formula that returns "", understand that this is not a blank cell, it's a cell with a formula, so Kelly's solution won't change how the cell plots. Change the "" in the formula to NA(), which gives you an ugly #N/A in the cell, but gives you the Interpolate behavior in the chart. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services - Tutorials and Custom Solutions - http://PeltierTech.com/ 2006 Excel User Conference, 19-21 April, Atlantic City, NJ http://peltiertech.com/Excel/ExcelUserConf06.html _______ "Sheila Innes" wrote in message ... Thanks Kelly. Problem - the Not Plotted is grayed out, so I can't mark it. Any ideas? "Kelly O'Day" wrote: Excel lets you choose how you can plot empty cells. As zeros, not plotted or interpolated. To make your choice, select your chart, go to Tools Options Charts and select the Not Plotted option button for empty cells. ....Kelly "Sheila Innes" wrote in message ... I have a spreadsheet with a two columns, one for expected enrolment numbers and the first column of actual enrolments. For April we expect 90, so have a blank cell to the right for actual. This is showing a line from the March enrolment of 3 down to 0. How do I get the line to stop at 3 until I put a figure in for April? |
#8
Posted to microsoft.public.excel.charting
|
|||
|
|||
Do not show blank cells as 0 in chart
Then make sure the cells are truly blank.
Is it an area chart? An area chart doesn't treat blanks or NA() the same way that line or XY charts do. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services - Tutorials and Custom Solutions - http://PeltierTech.com/ 2006 Excel User Conference, 19-21 April, Atlantic City, NJ http://peltiertech.com/Excel/ExcelUserConf06.html _______ "Sheila Innes" wrote in message ... Jon No, there are no formulas in these cells. Sheila "Jon Peltier" wrote: Sheila - Do you have formulas in Recruitment!$B$4:$B$9 and Recruitment!$O$4:$P$9? This is where you need to change "" to NA(), if it's a line or XY chart. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services - Tutorials and Custom Solutions - http://PeltierTech.com/ 2006 Excel User Conference, 19-21 April, Atlantic City, NJ http://peltiertech.com/Excel/ExcelUserConf06.html _______ "Sheila Innes" wrote in message ... Hi Jon I checked the chart and the 'not plotted' option is already selected. The equation for the chart is =Recruitment!$B$4:$B$9,Recruitment!$O$4:$P$9. The B selection is the month, the O is the actual and the P is the target. Any more help much appreciated. Regards Sheila "Jon Peltier" wrote: 1. Select the chart before going to Tools - Options - Charts. 2. What kind of chart is it? 3. If it's a line or XY chart, if you have a formula that returns "", understand that this is not a blank cell, it's a cell with a formula, so Kelly's solution won't change how the cell plots. Change the "" in the formula to NA(), which gives you an ugly #N/A in the cell, but gives you the Interpolate behavior in the chart. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services - Tutorials and Custom Solutions - http://PeltierTech.com/ 2006 Excel User Conference, 19-21 April, Atlantic City, NJ http://peltiertech.com/Excel/ExcelUserConf06.html _______ "Sheila Innes" wrote in message ... Thanks Kelly. Problem - the Not Plotted is grayed out, so I can't mark it. Any ideas? "Kelly O'Day" wrote: Excel lets you choose how you can plot empty cells. As zeros, not plotted or interpolated. To make your choice, select your chart, go to Tools Options Charts and select the Not Plotted option button for empty cells. ....Kelly "Sheila Innes" wrote in message ... I have a spreadsheet with a two columns, one for expected enrolment numbers and the first column of actual enrolments. For April we expect 90, so have a blank cell to the right for actual. This is showing a line from the March enrolment of 3 down to 0. How do I get the line to stop at 3 until I put a figure in for April? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
checking that cells have a value before the workbook will close | Excel Worksheet Functions | |||
Chart to display only bars for values that are > 3 & blank cells | Excel Worksheet Functions | |||
Activating a Chart object | Charts and Charting in Excel | |||
Condensing a list/range with blank cells to a new list/range without blanks | Excel Worksheet Functions | |||
Blank Cells in Pivot Tables | Excel Discussion (Misc queries) |