Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Hide Cells with Zero Value in Excel 2007 while using line chart ty
Hello All,
I am using Excel 2007 and have a situation with the charting functionality. For example - I am trying to set up automatic update chart update for Jan 08 €“ Dec 08 data i.e. user just enters data each month and the graph trend shows up each month. I have done this a lot of times but seem to have a unique situation with excel 07 this time when I am using the line chart type to show the trend. The date cells that have some kind of formulas used i.e. if function or Iserror function etc. I tried to test the chart by entering data for January it works fine with bar graph only displaying Jan 08 data and other months zero but when I switch the chart type to line chart instead of just showing a point for January 08 data it shows the Jan 08 data and other months as zero i.e. graph line drops from the value to zero. I tried to set the hidden empty cells as zero and gaps (Excel 07 option) but it did not work. Next I removed the formulas i.e. the IF function and other functions and manually entered the data€¦€¦line chart worked fine i.e. did not display the subsequent months data i.e. Feb 08 €“ Dec 08 which is all zero. This to me is weird that once you remove the formulas the line chart works fine by not displaying the zero cells in the chart but the bar chart works even though it has formula. Can someone please help me with my situation as I would like to automate the chart using the line graph and at the same time keep the formulas in the cells? |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Hide Cells with Zero Value in Excel 2007 while using line chart ty
Your formula is probably something like
=IF(something,value,"") The "" looks blank, but it's a piece of text, and Excel assigns a value of zero to text. In line and XY charts, if you change "" to NA(), the formula returns #N/A, which is not plotted with a marker. You can hide the error with conditional formatting. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Chicago" wrote in message ... Hello All, I am using Excel 2007 and have a situation with the charting functionality. For example - I am trying to set up automatic update chart update for Jan 08 - Dec 08 data i.e. user just enters data each month and the graph trend shows up each month. I have done this a lot of times but seem to have a unique situation with excel 07 this time when I am using the line chart type to show the trend. The date cells that have some kind of formulas used i.e. if function or Iserror function etc. I tried to test the chart by entering data for January it works fine with bar graph only displaying Jan 08 data and other months zero but when I switch the chart type to line chart instead of just showing a point for January 08 data it shows the Jan 08 data and other months as zero i.e. graph line drops from the value to zero. I tried to set the hidden empty cells as zero and gaps (Excel 07 option) but it did not work. Next I removed the formulas i.e. the IF function and other functions and manually entered the data..line chart worked fine i.e. did not display the subsequent month's data i.e. Feb 08 - Dec 08 which is all zero. This to me is weird that once you remove the formulas the line chart works fine by not displaying the zero cells in the chart but the bar chart works even though it has formula. Can someone please help me with my situation as I would like to automate the chart using the line graph and at the same time keep the formulas in the cells? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I need to hide points on a line chart if the value is ZERO | Charts and Charting in Excel | |||
Excel 2007 line-chart pasted as an enhanced metafile - editing | Charts and Charting in Excel | |||
Add a Horizontal Line to a Column Chart in XL 2007 | Charts and Charting in Excel | |||
how to hide zero values on line chart | Charts and Charting in Excel | |||
Excel - if cells = 0, how to conditionally hide rows in chart | Excel Worksheet Functions |