Not using zeros in graphing.

I have a running workbook that has tons of information.  I have added a sum 
page in order to have all the data summed up in one simple place.  I have 
formulas that read back into the workbook to link to a cell.  Depending on 
what moth it is, that cell could be empty as it is a yearly wookbook. For 
example, if this is August, then there is information in the workbook up to 
August, but none after.  With that said, the sum page has the #DIV/0! in the 
cell which essentially equals zero.  I also have graphs that I have linked to 
this sum page.  My problem is in order to keep the graphs up to date, I have 
to physically go back to each graph and move the data range.  I do this 
because if I select for example, January through December, the graph goes 
along till I have no data and drops to zero in the line graphing.  How do I 
prevent the graphs from using the zero(or the cell unless theres data there) 
to graph with?

Any help would be appreciated.

Thank you.
0
8/4/2005 4:01:37 PM
excel.charting 18370 articles. 0 followers. Follow

1 Replies
508 Views

Similar Articles

[PageSpeed] 4

Option 1: replace the formula that yields a error value to return a NA() 
instead of the error.  For example, if you have =a1/b1, use =if(b1=0,na
(),a1/b1).

Option 2: adapt the ideas behind Dynamic Charts (http://www.tushar-
mehta.com/excel/newsgroups/dynamic_charts/index.html) so that instead of 
using COUNTA() use COUNTIF() or some such alternative to decide how many 
cells to include in the plot.

-- 
Regards,

Tushar Mehta
www.tushar-mehta.com
Multi-disciplinary business expertise
  + Technology skills
    = Optimal solution to your business problem
Recipient Microsoft MVP award 2000-2005

In article <ABF5FC62-5843-443C-9AAD-C4D5852A2953@microsoft.com>, 
royojaqual@discussions.microsoft.com says...
> I have a running workbook that has tons of information.  I have added a sum 
> page in order to have all the data summed up in one simple place.  I have 
> formulas that read back into the workbook to link to a cell.  Depending on 
> what moth it is, that cell could be empty as it is a yearly wookbook. For 
> example, if this is August, then there is information in the workbook up to 
> August, but none after.  With that said, the sum page has the #DIV/0! in the 
> cell which essentially equals zero.  I also have graphs that I have linked to 
> this sum page.  My problem is in order to keep the graphs up to date, I have 
> to physically go back to each graph and move the data range.  I do this 
> because if I select for example, January through December, the graph goes 
> along till I have no data and drops to zero in the line graphing.  How do I 
> prevent the graphs from using the zero(or the cell unless theres data there) 
> to graph with?
> 
> Any help would be appreciated.
> 
> Thank you.
> 
0
Tushar
8/4/2005 5:38:27 PM
Reply:

Similar Artilces:

How do you get the attribute value using XPath in VB.Net 2003?
Hi, How do you get the attribute value using XPath in VB.Net 2003? Many thanks, aushknotes "aushknotes" <aushknotes@discussions.microsoft.com> wrote in message news:508426F2-1C8A-4AD2-A52E-B80B9798AC0C@microsoft.com... > Hi, > > How do you get the attribute value using XPath in VB.Net 2003? > Prefix @ to the name of the attribute value. XmlAttribute attrib = (XmlAttribute)dom.selectSingleNode("/path/@attributeName"); -- Anthony Jones - MVP ASP/ASP.NET aushknotes wrote: > How do you get the attribute value using XPath in VB.Net 2003? ...

Data Validation using List (But needs unique list in drop down lis
Hi all, In sheet 1, column A is my title name while column B is person name. Sheet 1 is my database where i do data entry in this. In sheet 2, contains my query page. In cell A5, i uses data validation - list, on this cell. Say in sheet 1 : column A column B XXXXXXX Mr A YYYYYYYY Mr A ZZZZZZZZ Mr A AAAAAAA Mr B WWWWW Mr C DDDDDDD Mr C But In sheet 2, cell A5, I saw in the drop down list as follows: Mr A Mr A Mr A Mr B Mr C Mr C But i want to see this in cell A5 instead (Unique name that is) : Mr A Mr B Mr C ...

Prob. when viewing my XL graph thru internet...
Hi. A problem which may or may not be due to Excel: I have some files, protected and with some sheets hidden, which I am using to show graphs and data on the internet. I want the viewer to be able to copy off the data (so they are saved as .xls files. The problem is, when I view the file in internet explorer, the chart sheet in which the file is opened will have a different size (the chart expands to fill the whole screen). The other chart sheets are fine. ??? Also, #1 the chart toolbar appears (don't want or need it) and #2 when I hit Back, a prompt to save appears (unwanted, ...

Use CountIf for a literal string
I am trying to count the number of cells in a range that contain the string <0.1. I don't want the number of cells that contain numbers less than 0.1, I want the actual string. Is there an escape character for comparison operators like the tilde for wild cards? Thanks, Chad Try this... =SUMPRODUCT(--(A1:A10="<0.1")) -- Biff Microsoft Excel MVP "chadkwelch" <chadkwelch@discussions.microsoft.com> wrote in message news:73F17509-1C52-4935-A45D-66DCC63B20C6@microsoft.com... >I am trying to count the number of cells in a range that ...

Posting Date used in Revenue Expense Deferral
I have a PM Invoice with Document Date 16/11/2009 and Posting Date of 01/12/2009. Entered Deferral details starting 01/12/2009. In GL the deferral charges commence 01/12/2009 and are all as expected. The Credit entry for the full invoice is posted to GL with posting date of 01/12/2009. The problem I have is that the Debit entry for the full invoice amount is posted to the document date of 16/11/2009 and I want it to be the same as the invoice posting date (01/12/2009) Is there a way to do this? Thanks Audrey ...

Can't Send Messages Using Outlook Web Access & XP
Our users with Windows XP cannot send messages using OWA. I've read a few KB articles about some incompatabilities between XP and OWA, but none of the proposed fixes (using the "basic" rather than "premium" client, or changing the security settings in IE) seem to solve this last problem. When they click on "send", it generates an unspecified "error on page". The problem occurs using IE and Firefox, but just on XP machines. My Windows 2000 machine works fine using IE and even my Macintosh using Safari (gasp!) works. ...

How do I specify an address when using "ActiveDocument.SendMail"
Hi Here is my code: If Not IsNull(([Forms]![Process Bookings]![Booking Form].Form.Email)) Then Options.SendMailAttach = True objWord.ActiveDocument.SendMail How do I specify the recipients address? Stapes ...

data labels disappear from graph when i close the worksheet
I have added datalabels to a bar graph. But they go away when i close the worksheet despite having saved the changes. Could any one elt me know why this is happening? Thanks ...

Could very much use a Field List
I apologize if this is out there already, but it seems that at least a few field names changed from 1.2 to 1.3. Although I had downloaded a field list before, from here, I cannot even find that original post, let alone any more recent that might contain the correct field names. I need to do some custom SQL and would prefer not to reinvent the wheel. Thanks in advance very much to anyone who could help. Bud Izen Salem Oregon Do you have MS Access? Make a new project and attach to the database as your data source. You will be able to see all the tables and the field names. It has been ...

How to Print string (Windows Printer) using RMS QSRules.
A windows printer is set as a receipt printer for a register. I need to print strings to this printer using RMS QSRules. How can I do this? I tried using Register.ActivePrinter.PrintNormal(Station as Long,Data as String) but it won't work. By the way, what is Station in the parameter? ...

Adding a VCard to an e-mail using Outlook 2000 and Outlook 2003.
Hello. I have a question. Does anyone here know how to add a VCard to an e-mail in Outlook 2000 as well as in Outlook 2003? I want to be able to do this after I click new and I have a new blank message on my screen. Any and all help would be greatly appreciated. Thank you. -- darylakagod Outlook provides no way to add a vCard .vcf file in this scenario. The built-in way to do it is to start with a contact, not a message, and choose Forward as vCard on the contact's Actions menu. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for Admin...

Can't use address book
I am not able to add contacts in my address book. I receive the error: You cannot create entries for this address book when I try to add a new entry. I use Office 2000, with full Outlook. I would like to have an address book again... I have uninstalled and reinstalled office 2000 - no change in the problem. You get this error in trying to create a new item in your contacts folder? (sounds like you are select tools | address book | highlight "outlook address book" and trying to create a new item. you can't create items in the "outlook address book". this f...

Monitoring directories using FindFirstChangeNotification
All, I would like to monitor a few different directories using FindFirstChangeNotification. I have successfully used it in the past to monitor one directory. All the directories I would like to monitor are on the root of a drive, but I do not want to monitor all the directories on the root. Example: the root of the T drive, T:\Dir1 T:\Dir2 T:\Test T:\source T:\Backup T:\Update How can I montor the T:\Test, T:\source, and T:\Backup at the same time using FindFirstChangeNotification? Thanks in advance. Hi, You need to use the following API's to monitor the changes in a specific ...

Using outlook for email and outlook express for newsgroups
Hi I had outlook set up for my email messages and outlook express set up for reading newsgroups - now when I go to the newsgroups oe is automatically checking for email messages and downloading them - this never happened before and I think the only thing different is that I have installed a broadband modem and am waiting on freeserve activating my account - could something have happened to change this, and if so how do I change it back. Thanks Sharon "Sharon" <sharon@nospam.freeserve.co.uk> wrote in message news:c80adp$dva$1@newsg1.svr.pol.co.uk... > Hi > > I ha...

Using a OR() like function in an IIF statement
Hello, I am trying to create an IIF statement to test if the first character in a field is a 1,2,8 or 9. Something like the following: IIf(Left([possible_SO_match],1)="1 or 2 or 8 or 9",[Possible_SO_Match],"No Match") Is there a way to create it without going to a 4 level nested IIF statement? Thanks, Kerry -- Message posted via http://www.accessmonster.com kkulakow via AccessMonster.com wrote: > Hello, I am trying to create an IIF statement to test if the first > character in a field is a 1,2,8 or 9. > Something like the following: > > IIf(Left([possib...

Print dialog box using VBA in excel
Hi Everyone, I like to add a print button (that will select several worksheets and print them) in my excel worksheet. I have recorded my action using the macro recorder the problem is I can't select the printer everytime I run the macro it print in the background without asking the user to select the printer and uses the default printer. Can anyone help?? I just want the user to be able to select the desired printer every time before printing. My current code: Sub Print_All() Sheets(Array("Sheet 1", "Sheet 2", "Sheet 3", "Sheet 5)).Select 'intent...

can cells apply conditional formatting using the internal clock?
I am using excell to keep track of my production schedule and I wanted to know if there was a way to tie the cells in a worksheet to the internal date and time in the computer,so that the cells will update automatically. Example: Row A10 would be my production start date, Row A1 would be my projected finish date, I would like the cells in between to go from green to red as I near the finish date without manually inputting the date in each cell. Can you help me? Thyanks Set the normal format as desired (I selected a Pattern of Green). Select A1:A10, then select Format | Conditional Fo...

Using DAO instead of ADO
I have an Access 2000 database that was converted from Access 97. On one of my users computers, her system was re-installed with Access 2000, and the database has not worked correctly since that time. If I remember correctly, we had to set up the computers for these users to use DAO over ADO. It's been over two years since we did this, and (I hate to admit it - but) I've forgotten to how to set up the DAO to take precedence over ADO. Can someone please remind me? I thought it was part of the Add-ins, but when I go into Add-in Manger, I have NO add-ins available. I've checked i...

How do a use a string as a param for a cmdlet when it contains opt
I have a script that cleans out old files, currently it is in this format write-host "\\sapecc01\Integration\SCC\Archive\* -Include *.txt" $a = Get-ChildItem \\sapecc01\Integration\SCC\Archive\* -Include *.txt foreach($x in $a) { $y = ((Get-Date) - $x.CreationTime).Days if ($y -gt 28 -and $x.PsISContainer -ne $True) {$x.Delete()} } #Keep DESADV for 28 days write-host "\\sapecc01\Integration\SCC\Archive\* -Include DESADV_*.xml" $a = Get-ChildItem \\sapecc01\Integration\SCC\Archive\* -Include DESADV_*.xml foreach($x in $a) ...

very simple and useful, email password recovery tool
Outlook Password Recovery is a easy-to-use and wide compatiable tool, capable of instantly recovering email passwords for popular email clients, such as Outlook, Outlook Express, Windows Mail, Incredimail, Eudora, etc. http://www.top-password.com/outlook-password-recovery.html -- johneou johneou wrote: > Outlook Password Recovery is a easy-to-use and wide compatiable tool, > capable of instantly recovering email passwords for popular email > clients, such as Outlook, Outlook Express, Windows Mail, Incredimail, > Eudora, etc. > > http://www.top-password.com/outlook-pa...

Using Access database to "populate" Excel Sheets
Please help!!! I am willing to PAY anyone who can get this to run fo me. I have been trying for 5 days now trying to use a DBVlookup function t populate fields in Excel. I used examples from 4 different forums usin this function, but I cannot get any of them to work. I am somewhat ne to VBA so forgive me if I am not making sense in my questions. I have Excel Spreadsheet called "Account_Number". It is set up a follows: Column A is called "Account Number"..... Column B is called "Looked u description in Access". Account Number Description...

Fonts do not print in colur used when document created.
When I create a document in either Word ot Publisher the text is not always printed in the colour selected. This problem seems particular to Red & Blue. can any one help please. Peeter Have you tried some maintenance on your printer? Are you saving as a PDF? This problem has been reported using Microsoft's save as add-in. -- Mary Sauer http://msauer.mvps.org/ "Peter Piper" <PeterPiper@discussions.microsoft.com> wrote in message news:76737C66-2B07-42DF-BFC5-828A14CE135F@microsoft.com... > When I create a document in either Word ot Publisher t...

Windows XP Embedded Service Pack 2 is anyone using this?
Is this something we would be able to use with RMS? Or is this just for devices? I love the quick bootup. I use the WEPOS operating system successfully in a number of installations. Microsoft certified WEPOS with RMS when they introduced version 2.0. Kinnard Kinnard L. Kohler Retail Management Systems of Arkansas 300 South Rodney Parham Road Parham Place - Suite 1 Little Rock, AR 72205-4747 (Tel) 501-412-5686 (Fax) 501-374-3636 Email: kinnardkohler@sbcglobal.net "Doug Pic-N-Pac" wrote: > Is this something we would be able to use with RMS? Or is this just for > de...

Need to insert a picture using a function
I would like to know if it is possible to insert a picture (.jpg) using a function. I have a simple quote sheet setup for my customers and would like to insert a picture of the product next to the quote information. Is it possible to insert a picture based on what i enter as my product number?? I know this is a vague question, sorry. Someone help please!!! Try this link for a possible solution:- http://www.mcgimpsey.com/excel/lookuppics.html -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 00/02/03 -------------...

Disclaimer using IMSEXT.DLL corrupting SMTP mail
I have installed IMSEXT.DLL and configured it to append a disclaimer to all outbound messages (article 258206). In order to work for our POP clients, I also set up the IMS to route all messages through the IS (article 238471). It works fine for messages originating from Outlook clients. Messages sent via SMTP from POP clients, or routed through the server via Custom Recipients get corrupted. Attachments (MIME) also get messed up. The simplest symptom to describe is that all text styling gets stripped. I haven't been able to find any more information on supporting non-Outlook client...