Charting blank cells as 0 when using dynamic ranges

tkos

New Member
Joined
Aug 6, 2002
Messages
16
In an Excel 2000 workbook, I'm using dynamic ranges to define a cell range. I need to use dynamic ranges because the data in the spreadsheet changes each day and re-creating all of the charts manually would be too much work. A simple example of the problem is below
Sheet1
A B C
Date Color Price
1/1/2009 Blue 10
1/2/2009 Blue 15
1/3/2009 Red 8
1/4/2009 Yellow 9
1/5/2009 Blue 8
1/6/2009 Green 7

In the example above the named ranges are:
"Date"-=INDIRECT("Sheet1!$A$2:$A$" & COUNTA(Sheet1!$A:$A),TRUE)
"Color"-=INDIRECT("Sheet1!$b$2:$b$" & COUNTA(Sheet1!$A:$A),TRUE)
"Price"-=INDIRECT("Sheet1!$c$2:$c$" & COUNTA(Sheet1!$A:$A),TRUE)
"BluePrice"-=IF(Color="Blue",Price,NA())

When a chart is created using the "Date" & "BluePrice" ranges, the "#N/A" values are plotted as zeros. Is there any way to create a chart using these ranges without have the zeros plotted? I didn't run into this problem in previous versions of Excel.

I would prever not to place the "blueprice" formula on a work sheet because the spreadsheet and creating the chart that way. The workbook I'm working with has hundreds of charts on it and placing all of the formulas on a sheet within the workbook would slow things down considerably.

Thanks for any help
 

Sal Paradise

Well-known Member
Joined
Oct 23, 2006
Messages
2,457
Select the chart.

Tools -> Options... -> [Chart] Tab ->

Active Chart -------------------------------------
Plot Empty Cells As (o) Not plotted (leave gaps)

-or-

(o) Interpolated

Depending on which you want.
 

tkos

New Member
Joined
Aug 6, 2002
Messages
16
Thanks for the reply Sal. It looks like I left out a key piece of information in my earlier post, I'm currently using Excel 2007. Your solution works perfectly in 2000, any ideas how to get it to work in 2007?
 

Forum statistics

Threads
1,084,878
Messages
5,380,415
Members
401,673
Latest member
Ali Balleya

Some videos you may like

This Week's Hot Topics

Top