|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
|
Thread Tools | Display Modes |
|
#1
February 11th, 2006, 05:39 PM
posted to microsoft.public.excel.charting
|
|||
|
|||
How to ignore blank cells
I have a chart which pulls data from various worksheets. The data changes monthly, so each new month there are lots of blank cells which don't yet have values. But the chart continues on for the blank days, giving each a value of zero (even though the cells contains spaces, not zero). Is there an option to not chart data points when they're blank (or zero)? Thanks! -- MattBeckwith ------------------------------------------------------------------------ MattBeckwith's Profile: http://www.excelforum.com/member.php...o&userid=30571 View this thread: http://www.excelforum.com/showthread...hreadid=511363 
|
|
#2
February 11th, 2006, 06:28 PM
posted to microsoft.public.excel.charting
|
|||
|
|||
|
How to ignore blank cells
1) Click chart to activate it
Use Tools | Options; open Chart tab; specify how empty cells are to be treated OR 2) Learn about dynamic charts at http://www.stfx.ca/people/bliengme/E...ps/Dynamic.htm OR 3) use =NA() in blank cells, see http://www.stfx.ca/people/bliengme/E...issingData.htm -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "MattBeckwith" wrote in message ... I have a chart which pulls data from various worksheets. The data changes monthly, so each new month there are lots of blank cells which don't yet have values. But the chart continues on for the blank days, giving each a value of zero (even though the cells contains spaces, not zero). Is there an option to not chart data points when they're blank (or zero)? Thanks! -- MattBeckwith ------------------------------------------------------------------------ MattBeckwith's Profile: http://www.excelforum.com/member.php...o&userid=30571 View this thread: http://www.excelforum.com/showthread...hreadid=511363
|
|
#3
February 11th, 2006, 07:32 PM
posted to microsoft.public.excel.charting
|
|||
|
|||
|
How to ignore blank cells
Bernard Liengme Wrote: 1) Click chart to activate it Use Tools | Options; open Chart tab; specify how empty cells are to be treated Actually, I had already set it to not plot empty cells, but it does anyway. It seems I can't attach an Excel file to this message, but if you give me your email address I'll send it to you. My email address is . Thanks. -- MattBeckwith ------------------------------------------------------------------------ MattBeckwith's Profile: http://www.excelforum.com/member.php...o&userid=30571 View this thread: http://www.excelforum.com/showthread...hreadid=511363
|
|
#4
February 12th, 2006, 01:51 AM
posted to microsoft.public.excel.charting
|
|||
|
|||
|
How to ignore blank cells
If the cell contains a formula, even one that returns "", it's not blank. It
contains a formula that returns a text item. Follow Bernard's instructions for using NA(). - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ "MattBeckwith" wrote in message news:MattBeckwith.232y4b_1139686501.7405@excelforu m-nospam.com... Bernard Liengme Wrote: 1) Click chart to activate it Use Tools | Options; open Chart tab; specify how empty cells are to be treated Actually, I had already set it to not plot empty cells, but it does anyway. It seems I can't attach an Excel file to this message, but if you give me your email address I'll send it to you. My email address is . Thanks. -- MattBeckwith
|
|
#5
February 12th, 2006, 04:31 PM
posted to microsoft.public.excel.charting
|
|||
|
|||
|
How to ignore blank cells
The referenced web page says: When an empty cell is filled with =NA(), Excel interpolates the missing data, joining adjacent data points I don't want the missing data points interpolated. However, I went ahead and tried the NA() approach, as follows: =IF('3'!B230,'3'!B23,NA()) But it just filled the cell with #NA -- MattBeckwith ------------------------------------------------------------------------ MattBeckwith's Profile: http://www.excelforum.com/member.php...o&userid=30571 View this thread: http://www.excelforum.com/showthread...hreadid=511363
|
|
#6
February 12th, 2006, 07:21 PM
posted to microsoft.public.excel.charting
|
|||
|
|||
|
How to ignore blank cells
Yes, that is what we want. Now make the chart.
-- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "MattBeckwith" wrote in message ... The referenced web page says: When an empty cell is filled with =NA(), Excel interpolates the missing data, joining adjacent data points I don't want the missing data points interpolated. However, I went ahead and tried the NA() approach, as follows: =IF('3'!B230,'3'!B23,NA()) But it just filled the cell with #NA -- MattBeckwith ------------------------------------------------------------------------ MattBeckwith's Profile: http://www.excelforum.com/member.php...o&userid=30571 View this thread: http://www.excelforum.com/showthread...hreadid=511363
|
|
#7
February 12th, 2006, 10:27 PM
posted to microsoft.public.excel.charting
|
|||
|
|||
|
How to ignore blank cells
(Reply made by mistake.) -- MattBeckwith ------------------------------------------------------------------------ MattBeckwith's Profile: http://www.excelforum.com/member.php...o&userid=30571 View this thread: http://www.excelforum.com/showthread...hreadid=511363
|
|
#8
February 19th, 2006, 04:08 PM
posted to microsoft.public.excel.charting
|
|||
|
|||
|
How to ignore blank cells
I have followed this thread and I have a particular problem that I thought it was intended to solve. I am plotting with dates on the X axis. The Y values are function determined. Using the IF function I have tried giving the cell a NA value but the plot then extrapolates between the points. I do not want to give it a zero value. But I actually want the gap. I suppose I could, using a macro, copy the series and then delete any cells with NA in them and then use this as the basis for the plot. Is there a way of avoiding this? Des M -- DesM ------------------------------------------------------------------------ DesM's Profile: http://www.excelforum.com/member.php...o&userid=24121 View this thread: http://www.excelforum.com/showthread...hreadid=511363
|
|
#9
February 19th, 2006, 07:40 PM
posted to microsoft.public.excel.charting
|
|||
|
|||
|
How to ignore blank cells
Check Andy Pope's site (http://andypope.info) for a technique to display a
gap for this situation. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ "DesM" wrote in message news  I have followed this thread and I have a particular problem that I thought it was intended to solve. I am plotting with dates on the X axis. The Y values are function determined. Using the IF function I have tried giving the cell a NA value but the plot then extrapolates between the points. I do not want to give it a zero value. But I actually want the gap. I suppose I could, using a macro, copy the series and then delete any cells with NA in them and then use this as the basis for the plot. Is there a way of avoiding this? Des M -- DesM ------------------------------------------------------------------------ DesM's Profile: http://www.excelforum.com/member.php...o&userid=24121 View this thread: http://www.excelforum.com/showthread...hreadid=511363
|
|
#10
February 19th, 2006, 08:51 PM
posted to microsoft.public.excel.charting
|
|||
|
|||
|
How to ignore blank cells
John, Thank you kindly for this. It works fine. I thouhgt that there might be some function like NA() that made the cell genuinely blank. Thanks again. Des M -- DesM ------------------------------------------------------------------------ DesM's Profile: http://www.excelforum.com/member.php...o&userid=24121 View this thread: http://www.excelforum.com/showthread...hreadid=511363
|
|
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Generating truly blank cells | Nat | Worksheet Functions | 4 | September 30th, 2005 11:39 PM |
| how do I get excel to ignore blank cells in formulas | rbignami | Worksheet Functions | 1 | May 6th, 2005 03:18 AM |
| Copy down - special to fill only the blank cells | Mike | General Discussion | 3 | April 18th, 2005 10:08 PM |
| blank cells | R.VENKATARAMAN | General Discussion | 1 | April 6th, 2005 10:25 AM |
| Problem with sorting a column of numbers (descending) - top two cells are blank. | LDH | Worksheet Functions | 1 | August 13th, 2004 07:08 PM |
Linear Mode
