|
||||||||||||||||||||
|
|||||||||||||||||||||
|
#1
05-05-2005, 12:06 PM
|
|||
|
|||
|
I want to ignore invalid values in scatter chart
I have an x-y scatter chart which uses values calculated from other sheets in
the workbook. Sometimes these values are invalid, and therefore I use "IF" to render the appropriate cell blank. The entire series then disappears from the chart. I just want that point to bu uncharted. Any ideas? 
|
|
#2
05-06-2005, 06:06 AM
|
|||
|
|||
|
Re: I want to ignore invalid values in scatter chart
Hi,
I can't reproduce you problem. Depending on the formula used my data points either do not show, as intended, or have the value zero. Are you using a formula like this? =IF( test , value , NA() ) Cheers Andy Phil Rowe wrote: > I have an x-y scatter chart which uses values calculated from other sheets in > the workbook. Sometimes these values are invalid, and therefore I use "IF" > to render the appropriate cell blank. The entire series then disappears from > the chart. I just want that point to bu uncharted. Any ideas? -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info
|
|
#3
05-06-2005, 10:06 AM
|
|||
|
|||
|
Re: I want to ignore invalid values in scatter chart
Excel cannot "render ... [a] cell blank" via an "IF" function, because
the IF function must return something, and MS has not defined a return value that would be equivalent to a blank cell. Most people think of returning "" as blanking the cell, but "" is a string, and Excel graphs strings as zero. As Andy suggested, NA() or equivalently #N/A will not plot, but it also does not cause a break in connecting lines on the chart. Also, #N/A is an error value that will propagate through formulas unless you filter it out with IF(ISNA()). Bottom line is that nothing behaves exactly like an empty cell other than a truly empty cell (i.e. deleting the formula). Jerry Phil Rowe wrote: > I have an x-y scatter chart which uses values calculated from other sheets in > the workbook. Sometimes these values are invalid, and therefore I use "IF" > to render the appropriate cell blank. The entire series then disappears from > the chart. I just want that point to bu uncharted. Any ideas?
|
 |
| Bookmarks |
New topics in Excel Charting
|
|
|
| Currently Active Users Viewing This Thread: 1 (0 members and 1 guests) | |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
