How to omit data in a chart when there are error values?

Hi there!

I've created a chart based on data who's values are variable. In certain 
cases the data on one end can show error values (#DIV0!, #NUM, etc..). In 
those cases the chart sees them as zero values.

Is there a way to automatically omit these values from the chart without 
manually changing the series range?

Thank you!

Bou (3)
6/10/2005 11:51:02 AM
excel.charting 18370 articles. 0 followers. Follow

2 Replies

Similar Articles

[PageSpeed] 48

Bou -

You can test for an error:


This way you get the #N/A error, which is not such a problem for a chart.

- Jon
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions

Bou wrote:

> Hi there!
> I've created a chart based on data who's values are variable. In certain 
> cases the data on one end can show error values (#DIV0!, #NUM, etc..). In 
> those cases the chart sees them as zero values.
> Is there a way to automatically omit these values from the chart without 
> manually changing the series range?
> Thank you!
> Bou
6/10/2005 5:44:48 PM
Jon, that worked.

Thank you a bunch!



Bou (3)
6/10/2005 7:51:03 PM

Similar Artilces:

disconne3ct or unlink data label from data etxt box in design view
Access 07 on WinXP Have used report wizard to create basic reports. Want to move the data labels in the headers independently of the data field. really want to "stack" multiple fields in header and deatil area. Isn;t there some way to disconnect the label from the data field to enable this? Because of gropupings and headings, I would like to move some of the lower level fields back to the left to improve readability. Anyone have way around this aggravation for me? JR - You can delete all the headers and then replace them with labels in whatever format you wan...

VBA Code to Export Data from Chart
I have a chart that is automatically produced from Cognos. I export the data into Excel, but the data for X and Y axis is contained in the chart. How do I export that data into a spreedsheet? Cognos Impromptu? I don't work with it directly myself, but our accounting department uses it to extract data from databases. Don't know that they build any charts with it. But... since data for a chart must becoming from a query somewhere, couldn't you create a query or to provide the data that's being used to make the chart as a table and then export the table to Excel...

Charting data by date
Hi, I'm new to Excell 2002, and have made worksheets with data refreshed from the Web. However I can't get it to show up on a chart with dates on the X axis. Have the axis formatted OK, but how does it collect its data? Have tried a simple worksheet with manually entered data & the dates entered in column A, but still can't get a chart to work. ...June. Jon Peltier has a charting tutorial that may help you get started: When you manually enter data and dates, what problems do you have with the chart? June wrote: >...

OWA Exchange 5.5 Internal Server Error
Outlook Web Access on Exchange Server 5.5 running on a Windows 2000 Server in mixed mode. Users go to url of They get the logon box and enter mailbox name. Then they get the Dialog box asking User name: and Password. The correct info is entered, but the browser just hangs there. If the IIS Server is restarted, not just the Website, but entire IIS, it will work for a while, sometimes a few minutes, sometime for hours. A few times, instead of just having the browser hang there, an error message was returned: HTTP 500.100 Internal Server Error ASP erro...

Formulas in source data
I have a chart with twelve months of data that is generated by using a fomula: Cell A1 has the following: =IF(A2>5,A2*A3,"") Cell B1 contains: =IF(B2>5,B2*B3,"") These fomulas go out for twelve cells and are the source data for my line chart. I have only the first six cells with data in them and when I set up my chart I get the first six data points plus six data points at the zero value of the Y-axis. Can I suppress the data points where the formula returns a null value? TIA. You can use the NA() function to do this. "Ken" <Ken@discussions.micro...

Data Error in MS Money 2002
I've been using Money 02 for yrs now and have always imported data from my bank's website. I just recently tried to import my monthly data and received the following msg, "The file you attempted to import appears to be invalid or contains corrupt data. Please download the file again. If this problem continues, contact your financial institution." I dbl checked format and called bank's IT support 2 times but no issues reported from their end. Is this a Money problem and has anyone experienced same? Please help. Thanks - Brian In, Bri...

how do I make text fit an organisation chart autoshape box
I have too much text to fit an organisation chart autoshape box. How do I make the box larger to fit the text? I always make an org chart using individual shapes and connectors. This makes for slightly more work, but it also provides absolute control over each individual formatting element of each chart element. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions _______ "Philip Runacres" <Philip> wrote in message news:6ACAD0B5-559E-42D9-A845-D730FCA57650@micros...

Cannot edit Excel data in a PowerPoint
I'm not sure which discussion group this applies to, so I'm posting it in both Excel and PowerPoint. I just started having problems with any Excel workbooks embedded into a PowerPoint presentation. It is a bit difficult to describe but I'll give it my best shot. Whenever I double-click an Excel object to edit the data, it opens up like normal and gives me all of the MS Excel options I normally get. But what is very VERY strange is that when I try to select a single cell (or row/column), it will take me into the MS Excel program and not allow me to edit anything ...

Form data entry
Is it possible to have a command button included on a form, that when clicked the current entry being entered is aborted, without the autonumber field being tripped to the next number. eg data entry started and autonumber field shows 7 - data entry aborted - nothing saved - next new entry shows 8 even although entry 7 does not exist. Thanks Sandy It is possible if the form is not based on the table. The form can have the required fields [except autonumber]. You may use the command button to append the record. -- ********* If the message was helpful to you, click Y...

Wfica32.exe Error
Hi Has anyone seen a Wfica32.exe error, when working with Great Plains in Citrix environment. I did explore the Citrix web site and noticed that this error is certainly related with Citrix but couldnt get a further lead on how we cant prevent it. Strangely enough there are more than 50 users accessing Great Plains through Citrix and the error occurs only for a single user (as far as I know). This error just shuts down Great Plains abruptly and citrix session hangs as well. Any help / idea will be appreciated. Regards Samit Sounds like a problem with the ICA client on the users PC. Since ...

Criteria to prompt for field and omit certain records
I have a query that prompts the user for the install number. I also need it to omit any records that have "obsolete" in any of the revision fields. Following is my SQL code. If I include the Not Like "*obs*" criteria, the install number is not included in the report. If I exclude the Not Like "*obs*" criteria, the install number is included in the report, however all records with a revision of Obsolete also show up. SELECT [Engineering Drawing Numbers].MasterProject, [Engineering Drawing Numbers].[INSTALL NUMBER], [Engineering Drawing Numbers].[INST...

Calculate Data
I am trying to sum information based on a timeframe. Please see exampl below NAME TIME DATE bob 4 2/14/2004 00:05:00 James 10 2/16/2004 7:00:00 bob 10 2/13/2004 19:00:00 I want to get the total time for bob between 7:00 PM and 7:00 AM eg IF(DATE is BETWEEN 2/13/2004 7:00 PM AND 2/14/2004 7:00 AM) SUM TIM for bob) Any ideas -- Message posted from One way: Say your criteria was in G1 (bob), G2 (2/13/2004 19:00) and G3 (2/14/2004 07:00): =SUMPRODUCT(--(A1:A100=G1),--(C1:C100>=G2),--(C1:C100<=G3...

Hard-code source data
Is there any method other than using Dynamic Names/OFFSET to force a chart to always use specific columns and rows, regardless of whether or not new ones are added. We have multiple spreadsheets that track monthly metrics. Each month a new column is added representing a new month of data. I always want to show columns B-M to refelct the past twelve months. Currently when I add a new column representing a new month,the charts dymanically change to show columns C-N and I have to change over 20 charts back to B-M. Thanks! Melanie Martin Melanie - The way to handle this is with dynami...

Some Error that I can't figure out.
Ok I am typing in a bunch of UPC numbers for my data sheet, It will be a couple hundred numbers. The UPC numbers are as follows 012569528628 027616149237 025192051029 012235111536 012569528734 012569508422 12569508439 86162180934 043396039490 043396603820 012569509023 012569509030 12569512429 12569512436 For some odd reason all the numbers without a zero are wrong. All of these numbers need 1 number 0 in front of them. As for the other numbers I get a little green arrow in the upper left corner of the cell. It tells me Something about "Number stored as text" (This is the one...

missing data after upgrade
I am using Windows XP. I upgraded from Office 2000 to Office 2003 and my e-mails in Outlook have gone missing. I searched my hard disk for *.pst files (including hidden files) but only one showed up which seems to be the new (empty) one. How do I get back my old e-mails? What size is the pst? "KOC" <> wrote in message news:5e7101c49265$02fb1ca0$a501280a@phx.gbl... > I am using Windows XP. I upgraded from Office 2000 to > Office 2003 and my e-mails in Outlook have gone missing. > > I searched my hard disk for *.pst files (i...

Colors for chart bars
Hi, in a document I want to add a bar chart. This document will is a paper and later converted to pdf. In the diagram I will have tree groups with 4 bars each. My question: which color would you take for the 4 adjacent bars? And what DPI? The diagram (figure in eps format) will be relatively small and I want that the reader can still distinguish among the bars when the paper is printed. Are there any preferred combinations of colors due to the "theory of colors"? Thank you. Chris If you're trying to get the most visually diverse colors, I would try to take colors from op...

export data from excel to adobe
If you have 2007, Microsoft has a free add-in that will add the ability to print to a file in .pdf format that works across all Office 2007 applications: If you don't have 2007, then one of the easiest to use .pdf file creators is probably PDFCreator from SourceForge: it installs as a virtual printer on your system and when you wish to create a .pdf file you simply print to that virtual printer. Instead of a sheet of paper, y...

how do you use data from a chart in a spreadsheet or data chart?
I made an X Y scatter chart in Excel for a time depth display chart. I now want to take that information and put it into a spreadsheet or data chart. The XY chart only shows some crosspoints but I need the data chart to automatically show me the points that are missing (not plotted on the XY chart) I can enter the information into Excel but it leaves a tremendous amount of blank points and I am not sure how to get them to automatically fill in. It is not the same distance between each of the points. If anyone can help me with this, I would be greatly appreciative. It isn't rea...

How to create data connections for tab-delimited data...
Hi, I am having difficulty with creating a tab-delimited data connection. I used the Excel Import Data Wizard with success. When I try to access the Data Connection to change my source file, for some reason I don't have access to the Properties Tab. Can you help explain what I am doing wrong? Thanks in advance for your assistance. -Greg ...

Off the chart Graph labels, that don't show up?
I have am using a bar graph and one bar is off the scale. In the 2003 version of excel the label for that bar becomes invisible, in older versions I could just move the label down so that it can be seen. I can click on the label and if I highlight it i can see the number, but I cannot get it to show on the graph. Has anyone run into this problem? You could add a series to hold a label within the chart: - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions _______ "Dawn&q...

data formatting
I formatted my cell to a dd/mm/yy format. On my old computer, I used to be able to enter dec 5 in a cell and it would automatically change it to 05/12/04. Now on my new computer when I enter dec 5 in a cell it gives me 01/12/05. How can I get it to enter the right day and year in a cell without actually having to write the year in? Hi this settings depends on your regional settings (difference between mm/dd/yyyy or dd/mm/yyyy settings). You may also have a look at the following addin: -- Regards Frank Kabel Frankfurt, Germany Cho...

Outlook 2007 error event id: 35
Per hour i receive about 12'000 of these errors in the event viewer on my vista laptop. And usualy my outlook at some point just goes into the "not responding" mode. Then I have to kill the process from the task manager to be able to reopen outlook just to see it freeze again after a couple of seconds. I tried the whole stop indexing service and reinstall index service procedure to no avail. Does anyone else have a solution to this very anoying problem? Fred <> wrote: > Per hour i receive about 12'000 of these errors in the...

Chart Disappeared
I have data on one worksheet and a chart on another. The chart seems to have resized itself or disappeared. If I click around, I can randomly find things like titles and stuff, and even edit the values. If I right click, I can change the chart type, and when I preview the chart type selection, the chart displays properly. I have a macro running that resizes the chart, based on a selection on the data worksheet, but I'm not sure how that could have messed it up. Any ideas? -- Coolboy55 ------------------------------------------------------------------------ Coolboy55's Profile:...

Pivot table question: How to display total and percent for data simultaneouly
I'd like to make a pivot chart that displays the same data both as a total and as a percentage at the same time: Example: Consider the following source data: Name Category Value a c1 10 a c2 20 b c1 5 b c2 30 It is easy enough to make a pivot table displaying: Sum of Value Category Name c1 c2 Grand Total a 10 20 30 b 5 30 35 Grand Total 15 50 65 It is also easy to display the percentages of the grand total for each entry. I'd like to display both simultaneously. Something like Name c...

File lock error
Hello all, We have users that work on MS Publisher 2003 documents on a shared folder. We keep getting an error when multiple users try to access the same file. The error is a file lock error. My question is this - Can MS publisher documents be shared so that multiple users can work on them ?? The NOS is Novell netware 6.5 Sp5. All clients are windows XP. thanks for any help in advance, Martin Reyes LHRIC ...