#N/A plotted as 0 on line chart

From all I've seen, #N/A should be the value to use if you do not want blanks 
to show on your line chart.

I have a table showing load times for two different databases over 30 days.

Sheet 1 shows the chart.
Sheet 2 contains VLOOKUP functions to pull the last 30 days worth of data 
from Sheet 3 (which has all the data starting early December)

If I have blanks on sheet 3, my VLOOKUP functions (Looking like this: 
"=IF(ISERR(VLOOKUP(I6,Driver!A:J,4,FALSE)),NA(),VLOOKUP(I6,Driver!A:J,4,FALSE))" ) correctly return "#N/A" to the cell.

When the line chart hits this particular "#N/A", it gets charted as 0.  I 
have the same spreadsheet, with much more manual process, which drops "#N/A" 
in these blanks using a simpler IF (=IF(Driver!E22="",NA(),Driver!E22)), and 
those instances result in a skipped value on the chart.

Anyone have any ideas how to get the VLOOKUP version running?
0
Utf
1/26/2010 9:06:20 PM
excel.charting 18370 articles. 0 followers. Follow

0 Replies
832 Views

Similar Articles

[PageSpeed] 33

Reply:

Similar Artilces:

possible to equisize charts (6) in chart sheet?
My chart sheet has six charts on it; arranged in two columns of three charts each. The "snap to grid" feature is nice yet I am having difficulty getting all six of the charts to be the same size/dimension. Is there a shortcut for doing this or is it necessary to simply "eyeball" the heights and widths of the charts? I am hoping there is a way to automatically "tile" the charts. Thank you. Andrew - I usually use a regular worksheet for this, a blank one without all those numbers cluttering it up. When you drag and stretch charts on a worksheet, holding...

SP1A on version 8.0
I just installed sp1a on version 8.0 The problem is the users that do not have admin rights get a message you do not have rights to run this application. They are set up as power users and all was well before the service pack upgrade. Would like to keep these user out of the admin group ...

Excel Charts
I would like to easily/quickly toggle series in an Excel chart on an off. What I would really like to have is some way to click on a serie in the legend next to my chart and have that series data disappear fro my graph. Then I need to be able to click that series again in th legend and have the data reappear on the graph. I don't think Excel ha this capability built-in, so I'll have to create an add-in or macro Any ideas -- Message posted from http://www.ExcelForum.com Hi! I think you might find this site will help: http://www.peltiertech.com/Excel/Charts/ChartByControl.html Alf ...

RMS 1.3 vs. 2.0
We are currently running RMS 1.3. We have access to the 2.0 SW version, but haven't been able to see a good comparison between 1.3 and 2.0. What does it deliver in addition to 1.3? What are the reasons people upgrade for? For 2.0 You do not need dongle anymore. It has 4GB DB Better feature for matrix item Better discount feature for discount items You can Mix and match by unit price and Mix and match : percent off and It has all the hotfixes. You should log in to customer source for more info. -- "sabribo" <sabribo@discussions.microsoft.com> wrote in message ne...

Is there a command line string I can use to send email?
If I can send email from a command prompt I can figure out a way to do a mass email to a list of people volunteering for something where I work - the list changes daily. Sandra <ab@mdahospital.com> wrote: > If I can send email from a command prompt I can figure out a way to > do a mass email to a list of people volunteering for something where > I work - the list changes daily. Check the tools here: http://www.slipstick.com/addins/mail.htm#massmail -- Brian Tillman ...

Charting in 2 axises
I need to chart on 2 axises showing the data as two separate columns next to each other. When I chart the two columns I lose my second axis on my chart. When I show my second axis on the chart, then the two columns are laid on top of each other. I want my second axis to show but I want the two columns to show as separate next to each other. What about changing your chart to a 3d model. 3d models can be panned to any angle which will enable you to see overlays. It's too bad there's not an image pasting function here. I have some graphs that are 3d that might make more sense ...

Changing line appearance for future values
Using Excel 2007. Is it possible to change the appearance of a line in a chart for values in the future (like projections)? I would like to go from solid to dashed and keep the same color and weight. Either you format line pieces individually (manual work). Either you use 2 series to plot in the chart. Use IF functions to see on which series a given value should be shown. Use NA() for values that should not be plotted. -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "RhysPieces" wrote: > Using Excel 2007. Is it possible to change the appearance of a line...

Referencing Chart Data?
I have a chart that is referencing a series of data that are formulas. The formulas return an average for the specified array. I am needing to find out how to keep the chart from considering the blank cells as zero values. I have tried having the formula return a "" (blank) value if false, but it still shows up in the chart as a zero value. Do you know the answer to this? -- Thank you! I don't know if this varies by version of Excel, so I'll just respond with what works in 2003: If you don't mind a continuous graph, try returning the N/A value by using ...

Protected XY plots change format spontaneously
I use Excel 2000 (9.0 SP3). In my workbook, each of 20 worksheets has some data-entry cells, an embedded XY plot, and some buttons for macros. Each plot has 1 series - the data from the data-entry cells on that sheet. Cells outside the data-entry area are locked. The worksheets are all "protected" so that users can make changes only in the data-entry cells. Two more XY plots are on separate sheets in this workbook - each has 12 series, and is also "protected". I have about 20 such workbooks. All are on a network drive and are accessed by users working at various PC...

Excel Table/Chart to JPEG for Web
I'm looking for a simple (read cheap) program that I can copy/paste or print into and adjust the pixel count (keeping aspect ratio) and resolution (dpi) for upload to web in jpeg format. Ideas? 1. Use gif or png, which are designed for line art and similar images, not jpg, which is designed for photos and is terrible at line art. 2. If you know VBA, you could use a command like this one from the immediate window or within a procedure: ActiveChart.Export ActiveWorkbook.Path & "\Chart001.png", "PNG" 3. Check out John Walkenbach's Chart Tools utility, a...

Fitting Line to Exponential Data
I should be able to do this but can't figure it out in Excel. I want to fit a line to some nonlinear data so I can project intermediate points. I gather I need the LOGEST function for this but I'm not able to make it work. Here are the X-Y pairs: X Y 4.5 0 6 18 10 49 20 77 45 99 Any help, with explanations, would be greatly appreciated so I can do this on my own next time. TIA John Smith - > I should be able to do this but can't figure it out in Excel. I want to fit a line to some nonlinear data so I can project intermediate points...

sample data for CRM 4.0?
I read in the CRM 4.0 implementation guide (yeah, I am reading it!) that CRM 4.0 sample data was supposedly available for download... has anyone located the link for this? The resource center isn't coming up with anything. Thanks! Nick -- Nick Doelman www.readybms.com The sample data will be released soon (for English) and other languages will follow in due course. Philip Richardson [MSFT] "Nick Doelman" wrote: > I read in the CRM 4.0 implementation guide (yeah, I am reading it!) that CRM > 4.0 sample data was supposedly available for download... has anyone loca...

Money 2005 charts
Just wondering if i should buy Money 2005... I know Money can generate bar graphs and pie graphs, but will it create line graphs of expenses/account balances? I just find this easier to view all of it on one graph with different lines. Some standard reports e.g. Net worth over time are already line graphs, and for most others you can customise the display type. -- Regards Bob Peel, Microsoft MVP - Money For UK tips & fixes see http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny. I do not respond to any emails that I have not specifically asked for. "Andrew Rooke&quo...

I want help with charts
I want to create a waterfall or bridging chart? have a look at http://peltiertech.com/Excel/Charts/Waterfall.html and http://peltiertech.com/Excel/Charts/Waterfall2.html -- John MOS Master Instructor Office 2000, 2002 & 2003 Please reply & rate any replies you get Ice Hockey rules (especially the Wightlink Raiders) "Marton" wrote: > I want to create a waterfall or bridging chart? ...

Linking VS6.0 DLL to .Net 2003
Please help me with this.. I have a DLL created in Visual C++ 6.0 ( VS 6.0 ). I am trying to use this DLL in a new wrapper application written using Visual Studio .net 2003 (C++ - MFC). My DLL has many methods which uses CString as arguements and return values. At first, I got a Link error as error LNK2019: unresolved external symbol "__declspec(dllimport) public: static void __cdecl CTestCommon::SetName(class ATL::CStringT<char,class StrTraitMFC_DLL<char,class ATL::ChTraitsCRT<char> > >)" (__imp_?SetName@CTestCommon@@SAXV?$CStringT@DV?$StrTraitMFC_DLL@DV?...

Error de instalacion en Microsoft CRM 3.0
Cerca del final, me sale esto: Action Microsoft.Crm.Setup.Server.GrantDatabaseAccessAction failed. Invalid object name 'dbo.fn_LocalTimeToUTC'. Invalid object name 'dbo.fn_UTCToLocalTime'. Invalid object name 'dbo.fn_RptBracket'. Invalid object name 'dbo.fn_GetFormatStrings'. Invalid object name 'dbo.fn_FindUserGuid'. Invalid object name 'dbo.fn_FindBusinessGuid'. lo que hice fue crear las funciones en las 2 bases de datos que el programa crea, solo para q las encontrata, despues de hacerlo le di retry y termino la instalacion bien. imagi...

Dynamically control how many lines on a graph?
I apologize if this is a FAQ, for some reason my newsreader isn't picking up any messages from this group (looks like no postings) so hopefully I'll be able to access responses, if not, I'll googlesearch for responses later today and tomorrow... ------------------------------------------------ I have many sets of data, all on the same sheet (in a repeated pattern- starting in row 1, then 11, then 21, etc) My single graph is based on named ranges, and the named range(s) are based on Steve Bullen's funchart1 (autoexpanding chart) to control how many x-axis values are shown- whic...

How to create image of windows ce 5.0???
hi i am newbee in the windows ce 5.0 .i have a system which have a windows ce 5.0 i want to store the image of the windows that is installed on the system.you can chcek the system as bellow http://wintec.en.alibaba.com/product/277764863-200077449/Mobile_Touch_POS_IDT600.html i want to store the system image file(NK.bin) to a desktop computer for backup purpose .Is there any software for windows ce 5.0 that can generate Nk.bin file during runing of windows (like Ghost software). waiting for reply thanks ref: http://codeconnect.net/microsoft-news.windowsce-platbuilder ...

Best way to add a chart?
Earlier I had asked for some help on how to proceed with this application. I have moved forward a little on my own but now I'm at a point where I really need a pro's recommendation. I am trying to build an application that will track printer usage. I am using a search interface that I downloaded form Allen Browne's web site (excellent stuff!!). My concept is this; 1. Interface allows user to choose search criteria based on type of pages printed, specific printer, and time frame of printer usage audit. 2. After user selects the criteria the form displays the filtered data and ...

New license key for upgrades from 3.0 to 4.0
Is there a process for obtaining a new license key that will be accepted during an upgrade from 3.0 to 4.0. My current 3.0 license is not being accepted! Thanks in advance, ~Maria Trial license keys are provided inthe readme that comes with the downloads. The rpocess for getting the "real" keys has not been announced. -- Matt Parks MVP - Dynamics CRM "Maria Greendyk" <MariaGreendyk@discussions.microsoft.com> wrote in message news:D5CAE82E-A779-4FFD-9D72-3E0E02D3580C@microsoft.com... Is there a process for obtaining a new license key that will be accepted d...

Adding multiple lines of text within one cell
Is there a way, in excel, to add multiple lines of text to a cell? For example, I want to add a few bullet points (need hard return) but I want them to all appear within one cell. Thanks! not sure about the bullet points bit .. but ALT & ENTER will give you a line return within a cell. -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Michael" <groups@enthios.com> wrote in message news:BE8BDB15.5157%groups@enthios.com... > Is there a way, in excel, to add multiple lines of text to a cell? > For example, I want t...

Formula for charts
Hi all, I have the following function define in one cell: =D49-D49*(B8^2) D49 is dynamic. However, B8 is a constant cell referring to a constant value. When I tried to copy the function to another cell, B8 is changed to B9. I only want D49 to change. How do I keep Excel from changing B8? Thanks! Hi, You need to use absolute references ($) rather then relative ones. =D49-D49*($B$8*$B$8) Cheers Andy PPP wrote: > Hi all, > > I have the following function define in one cell: =D49-D49*(B8^2) > > D49 is dynamic. However, B8 is a constant cell referring to a constant > v...

How do I create oversized Excel charts that will print directly
to a large format plotter. Setting page size and setting paper size in printer options does not work. Do you have the appropriate drivers for the plotter? Something made for Windows and for Office Applications? - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ solarex wrote: > to a large format plotter. Setting page size and setting paper size in > printer options does not work. ...

Can't Scroll Text in a Single-line CEdit
Hello all, I have a problem with single-line CEdit controls. I have one where I put text into it, using SetWindowText(). And then I select part of that text, using SetSel(). But I need the caret to end up at the beginning of the selection, not the end. The user needs to see the start of the selection. How do I put the caret at the beginning of the selection and scroll the text back so you can see the caret? I tried something like this (which doesn't work): int iSelStart = GetIndexOfSelectionStart( pMyCEdit ); int iSelEnd = GetIndexOfLastChar( pMyCEdit ); pMyCEdit->SendMessage(EM...

show 3 sets of data per data point in a scatter plot
I would like to be able to plot the pieces of info for each data point in a scatter plot and have all the information appear when the mouse hovers above each point, i.e.: Name of School, percentage of test takers, percentage passed. Is this possible? Hello, Marie! You wrote on Tue, 3 Oct 2006 08:08:02 -0700: M> I would like to be able to plot the pieces of info for each M> data point in a scatter plot and have all the information M> appear when the mouse hovers above each point, i.e.: Name of M> School, percentage of test takers, percentage passed. Is M> this possib...