Dynamic Chart with blanks

I am using a combo box to chart a data series (prices) versus another
series (GDP) depending on a ticker. For some of my tickers, the data
doesn't go back as far as GDP does. I would like to cut off my chart
to only go back as far as my price data does for each ticker. I've set
up a dynamic chart, but instead of cutting off at the earliest price
date, it just plots zeros for the earlier, non-existant data points.
My prices column contains the following:
=IF(OR(B135="",B139=""),"",B135/B139-1). Why is excel plotting my
blanks as zeros here? Also, I have Tools > Options > Charts set to
"Not Plotted (leave gaps).

Any help will be greatly appreciated.

Thanks,
Andrew

0
givensna (3)
10/19/2007 2:49:29 PM
excel 39879 articles. 2 followers. Follow

1 Replies
483 Views

Similar Articles

[PageSpeed] 15

Try


=IF(OR(B135="",B139=""),NA(),B135/B139-1)


then if you don't like the look of #N/A you can use white fonts and 
conditional formatting



-- 


Regards,


Peo Sjoblom


<givensna@gmail.com> wrote in message 
news:1192805369.256271.173310@v29g2000prd.googlegroups.com...
>I am using a combo box to chart a data series (prices) versus another
> series (GDP) depending on a ticker. For some of my tickers, the data
> doesn't go back as far as GDP does. I would like to cut off my chart
> to only go back as far as my price data does for each ticker. I've set
> up a dynamic chart, but instead of cutting off at the earliest price
> date, it just plots zeros for the earlier, non-existant data points.
> My prices column contains the following:
> =IF(OR(B135="",B139=""),"",B135/B139-1). Why is excel plotting my
> blanks as zeros here? Also, I have Tools > Options > Charts set to
> "Not Plotted (leave gaps).
>
> Any help will be greatly appreciated.
>
> Thanks,
> Andrew
> 


0
terre081 (3244)
10/19/2007 3:07:46 PM
Reply:

Similar Artilces:

How do I divide a single image into the slices of a pie chart?
I'm trying to divide the image of a silver dollar into the slices of a pie chart without having it repeat in each wedge Hi, Instructions can be found here http://www.andypope.info/charts/pieimage.htm Cheers Andy bruce h wrote: > I'm trying to divide the image of a silver dollar into the slices of a pie > chart without having it repeat in each wedge -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info ...

Hide pivot field button when printing charts or stop lables appearing behind it
Hi This should be simple but it's turning out to be a real pain :@( We have a pivot chart (pie) which has both data lables and values shown. The problem is that the pivot field always gets in the way of the lables etc. Everytime the report is run we have to manually move the data lables from behind the pivot field button before it can be printed. Does anyone know if there's a way to prevent the pivot field being printed or to force the chart not to drop lables behind pivot fields? Thanks very much Mark ...

Last 12 months chart
I have a whole lot of charts that feed off of data that is broken down into regions in the rows and months going across the top of columns. every thing i found so far on dynamic charts works with date ranges going down a column, not across. How do i get it to work the way my data is set up. Mccal213: Have you considered transposing your source data? Dates in rows is the convention because you have thousands of possible rows and only 256 columns. You can select your data, copy and transpose with paste special. try it on a copy sheet to see if you can live with the more standard approa...

Excel 2007 chart help
Hi gang new here - we just switched to 2007 and i am having a hard time creating a pie chart with the following info - WA 29 $835K GA 39 $335K FL 68 $1M CA 105 $1M TX 123 $5M when i try to make pie chart i select all the cells with my mouse - when i do that and go to insert and i can get it to work all that shows in my chart is TX 123 where the title is and on the right side just one blip $835K On the data i selected i have a green box around TX 123 then a blue box only on $5M and a purple box covering from $835k down to $1M i can not get this to work any help would be appreciated...

*URGENT* Adding a Scroll Bar to a chart
I have added a scroll bar to my chart, however as I select the right button of the scroll bar (my graph moves historically) and when I scroll left (my graph moves closer to the most recent period). Is there an easy way to do the opposite? Ideally I'd like to select the right button to move to the most recent period and the left scroll button to move to historical periods! Thanks! Hi, What method are you using to relate scrollbar information to changing chart data? Named range formula or Index formula in worksheet or something else. Cheers Andy -- Andy Pope, Microsoft MVP - E...

Is 'Status' not dynamic?
I note that the Status field for all tasks in Outlook 2002's 'Active Tasks' view says 'Not started'. Seems quite illogical - I'd have expected it to change to 'In Progress'? Does this field not change automatically at all, but has to be manually altered? If so, that isn't that a singularly weak design aspect? And on a synced PPC, Pocket Outlook shows the same active tasks correctly as 'Not Completed'. -- Terry, West Sussex, UK ...

Chart in Excel
I posted this on the Access newsgroup but did not get an answer. I have a form in Access with a chart which I am manipulating with VBA. This is the message I posted on the Access Newsgroup. I have a chart in a form. When I open the form it is blank until I click around the form. It then displays. I have tried playing with the Auto Activate property of the unbound object frame as well as including in the form open event Me.oleGraph.Verb = acOLEVerbOpen Me.oleGraph.Verb = acOLEVerbHide Me.oleGraph.Action = acOLEActivate Me.oleGraph.Requery in various combinations. the acOLEActivate displa...

Changing the Same thing on multiple charts
I have 36 line charts of data from the same 4 pilot groups and 2 comparison groups. After I got them all created, I had a request to increase the weight of the lines for readability on all of the charts. Is there a way to easily do this? Right now all I envision is clicking on all 216 lines! My guess is a macro which I know nothing about. On the same note, is there a way to have it change the colors all at once (for instance, take the yellow lines, make them red)? -- mea You can follow this procedure to make your task somewhat less tedious: Select an item on one chart (a series,...

how do I print a range without printing the blank pages?
I am looking for the command "don't print blank pages". I have a spreadsheet range 3 pages wide by 3 pages deep. I do not want page 2 and 3. Select what you want to print and do file>print area and set print area, then save workbook Regards, Peo Sjoblom "Chris VP" wrote: > I am looking for the command "don't print blank pages". I have a spreadsheet > range 3 pages wide by 3 pages deep. I do not want page 2 and 3. If the pages are blank that aren't wanted there is probably something else that is wrong. Cells with spaces, empty stri...

Method of adding a chart with a chart.add in a macro
Hi all, I am working with an excel based program that is in Excel 97 format. Upon opening the program in 2000 it does prompt me with the warning that there are macro compatibility issues. I have developed a macro in Excel 2000 to use data to create a chart but the script does not compile, it stops at the Chart.Add line ...a friend ran it with Excel 97 and it worked fine. I have been trying to find a solution to what seems to be a compatibility issue, or a Chart.Add issue, or whatever it is...I have little knowledge of excel and vb so hopefully this isn't too complicated. Here's th...

how do you change bars into lines on a chart
I am trying to chart multiple years with multiple data ie 2 yrs each with 2 business units, each having GM & OP i want all GM to be bars & all OP to be lines. Excell automatically does this, but not how i would like Please help Select the series you want to change and right click on it. You should see an option for Chart Type. Change from Line to Bar or vice versa. "BJ" <BJ@discussions.microsoft.com> wrote in message news:05DEADD3-09F5-4439-BCA4-B693CD6C97EB@microsoft.com... >I am trying to chart multiple years with multiple data > ie 2 yrs each with 2 ...

Defind Dynamic named Range up to first error
I want to define a named Range up to the first cell in a column that has a #value error. How could i find that cell? Any ideas Thx- Do you want to include that cell in the range? Are there any other types of errors in the range like #N/A, #NUM!, #DIV/0! ? Try something like this: =Sheet1!$A$1:INDEX(Sheet1!$A:$A,MATCH(TRUE,ISERROR(Sheet1!$A$1:$A$20),0)) -- Biff Microsoft Excel MVP "chg" <geier.chris@gmail.com> wrote in message news:4f5e5e46-5e41-467b-93ef-4d85691a7031@n31g2000vbt.googlegroups.com... >I want to define a named Range up to the firs...

Blank Cell vs Cell equal to Zero (0)
When creating a formula, is there a way to differentiate between a blank cell and a cell that's equal to 0? Here's my formula: =IF('2005 Sales'!O12>0,'2005 Sales'!O12,"") I wanted to change this to if O12 is greater than or equal to 0, then return O12, but I want it to return ("") if the contents of O12 is blank. Is there a way to do this? ... TIA! LavaDude What happens if O12 is *less* then zero? This will leave O12 blank ( "" ) if less then zero: =IF(AND(ISNUMBER(O12),O12>=0),O12,"") -- HTH, RD =========...

MS Chart
Hi all, how can I add data to each MS Chart rows during runtime? I haven't found so much information to work with VC++ and MSChart so any link or example would appreciate. Thanks in advance, Marc Soleda PD: Sorry for the repost but I found that this forum fits better with the question. ...

Microsoft support for Dynamics GP in virtualized environment
I am helping a client evaluate Dynamics GP in a fully virtualized environment. SQL Server, Citrix, and GP will all run on virtual servers. One question we have is whether Microsoft will provide support for Dynamics GP running in a client environment. One partner involved in the project claims that MS will not provide any support for virtualized GP. Another partner says that MS has a partnership with VMWare that offers to support all MS products on VMWare. Before I burn a support case, just wondering if anyone has firsthand knowledge of the MS support policy for GP in a virtualized ...

Remove Blank Pages 03-10-08
I have a report created on a query. After I get all the field on the report and everything formatted, when I go to print preview, I have blank pages between the pages that have the fields. For example, the report has 2 pages that have fields and a blank page between the first and second, and a blank page after the second, for a total of four pages. How can I reduce the blank pages? This is nearly always because the width of the report, or the width of its controls are too wide for your paper. Ensure that the width of your report in Design view is less than [the width of your paper MINUS...

org chart
As I move down the chart the print in the higher boxes shrinks. Any idea how I can stop this? If you are using XP or 2003 you can quickly fix the problem when the chart is complete. Select the top box. Choose Select|Branch from the Toolbar, set the Font size you want. Resizing the diagram might help, too tj ...

How to write a data in Excel file dynamically from C / VC++
Hi, Very Urgent... I Need to write a structure of data which is in "C" to excel file(*.xls). So, using C/VC++ how i can create a Excel file and how i can write a data into the respective cell at run time. Available VS .net 2003 and Excel 11. Pls help on this , Would be great if some samples provided. Thanks, Sweety Creating comma seperated file would be lot easier for you. If you put like this into a file:- My,Name,is,Sweety All the words above would appear on different cells when you open in excel. -- Vipin Aravind "sweety" <ursweety05@gmail.com>...

Pie chart labels move depending on PC used!
Hello, I have a spreadsheet which contains a number of pie charts with labels. I have moved most of the labels manually so they do not overlap. On screen they mostly appear ok and when I click 'save as' pdf they don't move to much (noting that there are some problems with the 'save as' functions). The problem is that when the same file is opened by a colleague, without making any changes, the labels have moved and now overlap. If I open the same file on my PC they are fine. Why does the latest version of Excel vary from PC to PC and is there a way to f...

Charting multiple worksheets
Windows Vista and Excel 2007 I am creating a weekly spreadsheet that collects the exact same data Monday through Friday. As data is entered on the worksheets a graph is dynamically created/updated. What I want to do is have a Weekly worksheet that totals all of the individual daily worksheets into a Weekly Graph. How do I select the cells from the daily worksheets to create this Weekly graph? thanks as always, d. :) -- dslocum Create a new table that sums the daily data into weekly numbers, than base your graph off the weekly results. "dslocum" wrote: > > Windo...

Conditional Chart/Graph?
I'll try to simplify things as much as possible. I currently have a numerically ordered list. I need to only display the list up to a certain number. The certain number which I need to list up to changes depending o other variables. I also need to display this restricted list in a graph/chart. I could conditionally format the list to only display numbers in readable font colour up to the certain number, however when i then pu the data into a chart, the values which are unreadable on the workshee (e.g. because they are the same colour as the background) are displaye in the graph. Any...

Data Table only
How can i get data to appear (in a data sheet if need be) but not the chart A data table is part of a chart, so you can't have a data table without a chart. Or maybe I misundersand your question. John Walkenbach For Excel tips, macros, & downloads... http://j-walk.com/ss "JD" <anonymous@discussions.microsoft.com> wrote in message news:056f01c3a31d$ef23dd70$a101280a@phx.gbl... > How can i get data to appear (in a data sheet if need be) > but not the chart If you already have the data in some cell range, isn't that range a data table in its own right? -...

Defining default fonts for charts
I have been unable to make my chart settings "stick"; I want my x-y scatter charts to have, for example, Arial 14 for title, Arial 11 for x an y axis titles, and Times New Roman 10 for axis number scale. I want all fonts to be non-scalable (by unchecking the appropriate box in the chart format box). Instead, every time I create an x-y scatter chart, I get ALL titles' text to be in Times New Roman, and scalable. How do I make these settings? This is especially a question for titles, since they don't even exist yet in the default template. The only thing I could sugge...

Dynamic "IN" criteria in query
I am in the midst of modifying an existing Access database used by a single office into one which is used by and stores data for multiple offices. Data for all offices is stored in the same database. When the database is initially opened a global variable is set to the value of the office_id(s) that user is associated with. This can be one number ("1") or multiple numbers ("1", "2", "3"). I need to update all of the existing queries in the database so that they only show data for customers who belong to the office_id(s) the current logged in user is asso...

What address do I enter to "connect to MS Dynamics CRM"?
I am trying to reinstall CRM 4.0 in Outlook. In the configuration wizard, i get to a screen that asks "Connect to MS Dynamics CRM at:". 1. Do I indicate 'my company' or 'an online service provider'? We're a bunch of one-person offices and have no server. We access CRM online by going on the Internet. 2. Regardless of which choice I make above, I have to enter an address in the next screen. I tried the address that we use when we go online, but the program won't accept it. The address is https://[companyname].crm.dynamics.com/loader.aspx Can anyone help...