how to make gaps in plotted data when cell has formula

I have a simple data set with a value for each month except June.

I want the chart to reflect a gap for June, not a zero value.  Although I
did tools, options, chart, make empty cells plot as gaps,   it still plots
the June value as a zero.

In an attempt to get rid of div/o error,  I have a formula  in the data set
as If (b5=0, "", c5/b5) . What I'm plotting is the quotient c/b.

We have determined that the formula is what is causing the problem.
Interestingly, if I open this same worksheet (as is) in quattro pro, the gap
appears in the chart.

Is there a different way to do this? thanks.

Douglas E. Eglen
Kokomo, IN

deglen (1)
12/4/2003 8:25:35 PM
excel.charting 18370 articles. 0 followers. Follow

1 Replies

Similar Articles

[PageSpeed] 38

Yes.  Replace the "" by NA()

If this messes up subsequent formulas, use a dummy range for plotting 

If you don't like the resulting #N/A display, see Debra Dalgleish's


Tushar Mehta, MS MVP -- Excel
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <bqo4k2$kmn$>, says...
> I have a simple data set with a value for each month except June.
> I want the chart to reflect a gap for June, not a zero value.  Although I
> did tools, options, chart, make empty cells plot as gaps,   it still plots
> the June value as a zero.
> In an attempt to get rid of div/o error,  I have a formula  in the data set
> as If (b5=0, "", c5/b5) . What I'm plotting is the quotient c/b.
> We have determined that the formula is what is causing the problem.
> Interestingly, if I open this same worksheet (as is) in quattro pro, the gap
> appears in the chart.
> Is there a different way to do this? thanks.
12/4/2003 9:19:02 PM

Similar Artilces:

Make "Outlook Today" update appointment instance, not the series?
When I click on a recurring appointment entry in "Outlook Today" view, it updates the series. Is there any way to get it to update just that day's instance, without going to the calendar, double-clicking the appointment, then selecting the "update this instance" radio-button? No. -- Jocelyn Fiorello MVP - Outlook *** Messages sent to my e-mail address will NOT be answered -- please reply only to the newsgroup to preserve the message thread. *** In, aveit wrote: > When I click on a recurring appointmen...

macros randbetween formulas
i would like to make a "button" on the spreed sheet it self were every time i press it, it runs the Randbetween formual and place it in a cell, one is this possable and can someone show me what i got to do, compleatly have not idear how to wright mircos thanks It is possible but you have to be more specific about your needs. At present, all I can work out is that you want a button to enter a Randbetwee(0 formula in an unspecified blank cell. For example, how does the function get its values; which blank cell? When you give further detail, think of yourself in the position o...

xl 2007 how to validate that cell 1 invalid if cell 2 not blank
microsoft office 2007 xl - how do you validate a cell such that you cannot enter a value in cell 1 if a value has been entered in cell 2? ...

Printing hidden cells
Have hidden cells but want the contents (which are the score the child was given)to be printed, how please? TIA You can't unless you unhide them. You could use a macro that unhides them, prints the document, then hides them again. This macro would be simple enough to record (Tools-->Macro-->Record New Macro). -- _______________________ Robert Rosenberg R-COR Consulting Services Microsoft MVP - Excel "Chris Meech" <> wrote in message news:17d1301c419cf$0ab2c830$a501280a@phx.gbl... > Have hidden cells but ...

What are the performance enhancements to Crm 4 in the data tier
Microsoft released the Microsoft Dynamics CRM 4.0 Performance and Scalability White Papers: Does anyone know of similar papers that indicate similar studies for Crm 3? Ultimately I collecting information to justify upgrading from 3 to 4 and our current bottleneck is the sql server. Hi Rob, CRM 4.0 offers a number of advantages to CRM 3.0 when it comes to performance and scalability. For example, support for 64-bit servers, server role separation, cluster database server supp...

Data Validation #23
Rather than writing some VBA to restrict entries into a cell I thought I would use the data validation! All I am trying to do is restrict the user to having to select from the from pick from list box. I believe I have set things up correctly, but I cannot stop the use from adding spaces! The use is restricted from using his own codes, but I cannot seem to stop him from manually adding (in error) unnecessary spaces either before or after his selection. What happens is he selects from the list, but he can them manually edit the cell. It does not allow him to use entries that are not ...

Problem with extending list formats and formulas
Hey all- I'm having a problem with extending list formats and formulas using Excel 2000. I know there are limitations to what that feature can do, but I thought I'd check here, since I have a very simple example that isn't working. I create a sheet with two columns. In the column A, I just enter any arbitrary number in each of the first 10 cells. For column B, I want to calculate the difference between the last two values in column A. In other words, B2's formula is =A2-A1, and B3 = A3-A2, etc.. I copy this formula down the first 10 cells of column B (skipping the first r...

does publisher allow you to make door knob flyers
I need to know if microsoft publisher allow you or helps you create the flyers that go on doors Mary Sauer has a great website with many helpful templates, suggestions and projects including one for door hangers. Go to the bottom of the page then just above a picture of George Washington is the door hanger file. "askaboutit" <> wrote in message >I need to know if microsoft publisher allow you or helps you create the > ...

sum weekly data into monthly
I want to sum weekly data into monthly data by SKU. I have two tables. the first looks like this: sku 20100426 20100503 20100510 20100517 20100524 abc 200 300 200 150 150 where the column heading of 20100426 = week ending 4/26/2010 the second table looks like this: DATE Fiscal Month 20100426 6 20100503 6 20100510 6 20100517 7 20100524 7 I want to sum SKU abc by fiscal mont. For month 6 it would equal 700 month 7 would equal 300. hate to admit it but this one is beyond me. ?!? You have a column named for the "week ending date?&...

Excel Cell Formatting question
I am using MS Excel 2003 SP1. I would like to have negative numbers display as red and in brackets ) Any help would be greatly appreciated. Thanks To -- Tom Bradstree ----------------------------------------------------------------------- Tom Bradstreet's Profile: View this thread: Select the range you want to format and go to the Format menu, selec Cells. Then select the Number tab and select your number of decima places and the format for negative numbers ...

How to make one CSV file from two Excel sheets?
I have a huge database with about 400 columns. They are divided into two sheets - due to Excel limitation 256 columns in one sheet. I need to make one CSV file from these both sheets. The last column of first sheet should be followed by the first column of the second sheet. Is there any way how to do it directly from Excel? Or do you know any simple utility that can put together two CSV files exported from Excel? Thanks for any help Marek L. That sort of stiching can only be done using a hex editor. (And not all hex editors are created equal.) I did a quick search on the web and ther...

PivotTable:cell formating
Hi, All! How can I tell to Excel EP PivotTable get cell's format from OLAP-cube? -- - wbr Andrey Savchenko ...

Automatic Moving to the Next Cell
When using Excel to produce forms, I want to automate how the cells are selected when inputing data. Usually, I unlock the cells that will have data entered in them and lock the remaing cells. Next, I protect the sheet. Then, I use the tab key to move to the next cell that requires data instead of having to click on the next cell. I'd like to take it a step further. Does anyone know if I can direct Excel to automatically move to the next cell when I enter data in the current cell? These particular cells only have one digit entered in each cell. So, without having to use the ta...

Adding Source Data to a Chart.
I want to add data from 2 different sheets within the same work book added together. ie say data from Sheet1 B1 + Sheet 3 B1 as source data for the same bar on the chart. Can this be done direct or do I have to add them together first then use that as the source data. I have tried typically the following: Sheet1!$B$1+Sheet3!$b$1 and several variations of this without success. One way to do it is to have 2 series, one for each sheet, and make a Stacked bar chart. The only problem is that you don't get to see the total when adding labels to the series. Regards Esteban "Dizz...

Weird Formula Problem
I have a worksheet with some basic entries and formulas in Excel 2003SP2. Column A has some currency listed. In cell B1 I enter the formula =A1*.20 in order to get 20% of A1. The result appears fine. Then I drag the cell-formula down over the rest of column B. However, instead of showing the correct values, it simply repeats the result of B1. If I click on one of the cells, it shows the proper formula (A2*.20, A3*.20, etc.), but it won't show the correct result. I tried selecting the original formula and selecting a target cell and choosing Paste Special and selecting just the...

Import Data into Visio
Visio Newbie I am going thru a process of design and documentation of a SQL database. We have used Excel to define the design for each of the tables. The Excel includes the descriptions and default values. I would like to import (from Excel) this range of data. This will represent the table layouts/design for the SQL database table. The range I would like to import has a minimum of two columns of data. The one is the column name and the other is the datatype. Can I import this kind of data into a Visio object (UML Package maybe)? I would like to use this Visio Object illustrate ...

Run a Query on External data(ODBC) criteria based on input of a ce
I have an access database that I would like to put a DATE into a cell and have it auto run a query with that date criteria. I have done this with the query wizard and manualy but would like to automate the process by just typing the date and pressing the enter any one who could lead me in the right direction or who has done this before I would appreciate the help -- thank you TR Hi As an example here is a part of the procedure, I used for such a task (with ODBC for VisualFox tables, but only query syntax is affected by driver used) Public Sub RefreshQueryes() month= ActiveSheet.Ra...

Formula #value! Error,
Got this far, don't know how to negate #Value! error, in equation: (thanks) =IF(TODAY()>=DATE(MID(T9,2,2)+100,MID(T9,4,2),MID(T9,6,2)-$H$7)),"dd", to do with, if not have :yymmdd in left of general text cell T9... or even if the dd portion is not present. You can use a variation of the following formula to change the error result of a formula, but it requires that you write the formula twice, once as a condition, and once as the false result: =IF(ISERROR(Formula_To_Evaluate),ErrorValue,Formula_To_Evaluate) Here is another expample, using the PMT function. =IF(ISERRO...

For all rows, if cell has "X", get number from range?
I'm hoping this can be done with a formula - I'd like to stay away from macros unless I have to. This is a job order tracker. Each job order entry takes four rows. The first few columns are job info; the next 31 columns are the dates of the month. The first row of each job order is for days worked - if that job was worked on that day, an "X" goes in the row under that date. I'd like to put a formula at the end of each job order that captures all the dates the job was worked, as indicated by the "X" in that column in that job order. Something like this: 1...

cell comment limits
Hi All can't find this is excel specifications - is there any limit to the number of characters that a cell comment can contain? Cheers JulieD JulieD, from a previous post, by Chip I'm not sure, so I pasted about 60K of text into a comment. It chopped it off at about 11K. Interestingly, it chopped it off at 255 lines, which may be the limiting factor. Intuitively, I'd guess 32K total, but only 255 lines. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC -- Paul B Always backup your data befor...

data from a formula direcly to a variable
hi all, i'm using formulaarray but i would like to save time and direcly the data from the formula to transfer to variable instead on having in on a cell, it a metter of saving time and machine time is there an option for this? this is my code: xlApp.Range("S" & row).Select Selection.FormulaArray =" " how can i direcly transfer the data to a variable? Get rid of the selects xlApp.Range("S" & row).FormulaArray =" " -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) <> w...

Creating Formula
To All I am using Excel and OLE to generate a maintenance checklist spreadsheet , the spreadsheet is then downloaded to a palm device for field technicians to complete - only a date is required to be entered , what I would like is when the date cell is entered the current date is entered automatically ( saving the tech on entering a lot of dates on the small palm screen ) Something like this oSheet:Cells(nRow,"H"):Formula := "=FillDate()" Can someone assist me with an Excel formula to do this and how I associate it with a template sheet. Thanks for any help Colin ...

How to make body text indentation follow heading indentation?
I am not sure if this is even possible in Word. I would like to have the indentation of body text paragraphs to be the same as that of their header. Can someone tell me how to do this in Word 2007? Example: HEADING 1 Some text under heading 1. (no indent) HEADING 2 Some text under heading 2. (same indent as heading) HEADING 3 Some text under heading 3. (etc.) Create Paragraph Styles for each of the paragraphs to be used under the Headings and modify each Heading Style to set the appropriate indented paragraph style to be used for the followi...

Averaging every 5th cell while omitting zeros
Greetings! Thank you for your interest in my question, I have been bashing my head in trying to figure it out. In a column, I want to add every FIFTH cell starting with row 7 and ending with row 272. Meanwhile, I need to exclude all the cells with zero so the averaging only divides by the number of cells with a numeral. I have tried entering each 5th row individually in various formulas and I have tried defining a name and using that in the formulas but nothing has worked, I keep getting an error each time. I would really appreciate any help! Thank you. Erica, I don't li...

VBA Range Syntax Range(A2:???????) where ??? is Last used cell Col
Using XL 2003 & 97 What is the correct syntax for Range() as per the Subject Line? The following works fine: Set MyRange = Worksheets("Sheet1").Range("A2:A10000").SpecialCells(xlCellTypeBlanks) But, I want Range("A2:A10000") to be A2 : to the Last used cell Col A Any thoughts? TIA Dennis How about: with worksheets("sheet1") set myrange = .range("a2",.cells(.rows.count,"A").end(xlup)) end with You can add the .specialcells() stuff in that line or later. Dennis wrote: > > Using XL 2003 & 97 > > What...