Missing lines in chart w/x-axis with months 1-24...

First post from a "casual" Excel user (more familiar with SigmaPlot)...

Client wants a line chart with x-axis linear (baeline to 36 months) but 
there are only data for 2 baseline conditions and certain months:
on-drug
off-drug
M3
M6
M12
M24
M36

The Y axis (# of pellets) is nothing special.

I entered blank rows, corresponding to the missing months, into the 
datasheet, like so:
on-drug
off-drug


M3


M6





M12 (etc.)

This "method" plots the data points okay, but there are 2 problems:
(a) there are no lines connecting them, and 
(b) the x-axis labels are lousy

I worked around "b" by manually adding text boxes instead of x-axis labels, 
but the missing lines problem is a major pain (I manually added lines, but 
sometimes they don't hit the data points on center, and any resizing is a 
bear!).

Is there a better way for Excel to deal with missing data and draw the lines 
automatically, as it does if there are no empty rows in my datasheet?

Any help appreciated.  Sorry for the length, but I figured it's better to be 
specific.

Thanx
0
DendWrite (4)
5/9/2005 8:45:58 PM
excel.charting 18370 articles. 0 followers. Follow

2 Replies
596 Views

Similar Articles

[PageSpeed] 2

Hi,

In order to make things easier for the chart wizard to guess your data 
layout try adding a little more information to your data when you create 
the chart.

B1: =# Pellets
A2: =""
A3: =""
A4: =M3
B4: =1   ' replace 1 with real data
A7: =M6
B7: =1   ' replace 1 with real data
A13: =M12
B13: =1   ' replace 1 with real data
A25: =M24
B25: =1   ' replace 1 with real data
A37: =M36
B37: =1   ' replace 1 with real data

Leave the cells not referenced blank.
Now select the range A1:B37 and create a Line chart using the chart 
wizard. With the chart selected use the menu Tools > Options. On the 
chart tab make sure the 'Plot empty cells as: Interpolated' is checked.

You may need to alter the 'Number of Categories between tickmarks' to 1 
in order to see the M labels. Double click the x axis and go to the 
Scale tab to change this value.

Cheers
Andy


DendWrite wrote:
> First post from a "casual" Excel user (more familiar with SigmaPlot)...
> 
> Client wants a line chart with x-axis linear (baeline to 36 months) but 
> there are only data for 2 baseline conditions and certain months:
> on-drug
> off-drug
> M3
> M6
> M12
> M24
> M36
> 
> The Y axis (# of pellets) is nothing special.
> 
> I entered blank rows, corresponding to the missing months, into the 
> datasheet, like so:
> on-drug
> off-drug
> 
> 
> M3
> 
> 
> M6
> 
> 
> 
> 
> 
> M12 (etc.)
> 
> This "method" plots the data points okay, but there are 2 problems:
> (a) there are no lines connecting them, and 
> (b) the x-axis labels are lousy
> 
> I worked around "b" by manually adding text boxes instead of x-axis labels, 
> but the missing lines problem is a major pain (I manually added lines, but 
> sometimes they don't hit the data points on center, and any resizing is a 
> bear!).
> 
> Is there a better way for Excel to deal with missing data and draw the lines 
> automatically, as it does if there are no empty rows in my datasheet?
> 
> Any help appreciated.  Sorry for the length, but I figured it's better to be 
> specific.
> 
> Thanx

-- 

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
0
andy9699 (3616)
5/10/2005 7:58:39 AM
Andy, 

(Hey na, hey na...) The lines are back!  That works quite nicely (even on my 
existing graph).

Thanks very much,
Mike



"Andy Pope" wrote:

> Hi,
> 
> In order to make things easier for the chart wizard to guess your data 
> layout try adding a little more information to your data when you create 
> the chart.
> 
> B1: =# Pellets
> A2: =""
> A3: =""
> A4: =M3
> B4: =1   ' replace 1 with real data
> A7: =M6
> B7: =1   ' replace 1 with real data
> A13: =M12
> B13: =1   ' replace 1 with real data
> A25: =M24
> B25: =1   ' replace 1 with real data
> A37: =M36
> B37: =1   ' replace 1 with real data
> 
> Leave the cells not referenced blank.
> Now select the range A1:B37 and create a Line chart using the chart 
> wizard. With the chart selected use the menu Tools > Options. On the 
> chart tab make sure the 'Plot empty cells as: Interpolated' is checked.
> 
> You may need to alter the 'Number of Categories between tickmarks' to 1 
> in order to see the M labels. Double click the x axis and go to the 
> Scale tab to change this value.
> 
> Cheers
> Andy
> 
> 
> DendWrite wrote:
> > First post from a "casual" Excel user (more familiar with SigmaPlot)...
> > 
> > Client wants a line chart with x-axis linear (baeline to 36 months) but 
> > there are only data for 2 baseline conditions and certain months:
> > on-drug
> > off-drug
> > M3
> > M6
> > M12
> > M24
> > M36
> > 
> > The Y axis (# of pellets) is nothing special.
> > 
> > I entered blank rows, corresponding to the missing months, into the 
> > datasheet, like so:
> > on-drug
> > off-drug
> > 
> > 
> > M3
> > 
> > 
> > M6
> > 
> > 
> > 
> > 
> > 
> > M12 (etc.)
> > 
> > This "method" plots the data points okay, but there are 2 problems:
> > (a) there are no lines connecting them, and 
> > (b) the x-axis labels are lousy
> > 
> > I worked around "b" by manually adding text boxes instead of x-axis labels, 
> > but the missing lines problem is a major pain (I manually added lines, but 
> > sometimes they don't hit the data points on center, and any resizing is a 
> > bear!).
> > 
> > Is there a better way for Excel to deal with missing data and draw the lines 
> > automatically, as it does if there are no empty rows in my datasheet?
> > 
> > Any help appreciated.  Sorry for the length, but I figured it's better to be 
> > specific.
> > 
> > Thanx
> 
> -- 
> 
> Andy Pope, Microsoft MVP - Excel
> http://www.andypope.info
> 
0
DendWrite (4)
5/10/2005 8:04:02 PM
Reply:

Similar Artilces:

How to select series in chart?
I know I asked this question before, but (sigh) I cannot find the answer now, when I need it of course. How can I select a series in an Excel chart (XY Scatter) using the keyboard, not the mouse? The issue is: I have overlapping series, so it is difficult for me to select a series by moving the mouse cursor to a point in one series and right-clicking it, as I normally do. Someone once mentioned a ctrl and/or shift key combination (I think) that would allow me to select each series explicit in round-robin fashion. That is what I am looking for again. More generally, how could I have found...

double clicking and draging a column in a chart to chg data
in Excel 2003, double clicking on a column in a chart and then dragging the column up or down would change the data in a table upon which the chart depended. How does one do this in excel 2007? Tom Hi, That feature has been removed in 2007, there is no way to do it. If this helps, click the Yes button. -- Thanks, Shane Devenshire "Tom of inns" wrote: > in Excel 2003, double clicking on a column in a chart and then dragging the > column up or down would change the data in a table upon which the chart > depended. > > How does one do this in excel 2007? &g...

Changing SQL Server for CRM 1.2
As my SQL server is currently on its last breath I am in the process of moving our CRM 1.2 databases to a new dedicated serever. I have detatched the databases from the old server, copied the files to the server and attached them. I have then gone into CRM deployment manager and changed to the SQL server to the new server. That all appears to work fine, no error messages. But when users try to access the CRM system they get an error and the CRM server event log fills with error messages. A few are listed below: dmLog: Failed to execute the SQL batch in the file sqlbatch.sql. MSCRM P...

Can't insert Char(1) into SQL Server from Stored Procedure
Hello I am trying to insert a char(1) field into a table from an ASP.Net/C# application. At first I was inserting rows into the table but none of the columns that I was inserting the rows into were char(1) columns, they were mostly int and varchar columns. Then I found out that there was one column in the list that was not supposed to be an int column but instead it was supposed to be one of the char(1) columns into the table. So I replaced the int column in the list with the char(1) column and since the value of the char(1) column resulted from a CheckBox ('Y...

How do you turn off/on the "1" or "2" or "+" or "-" view hide/unh.
What is or how do you control, turn on/off the small "1" "2" or "+" "-" hide/unhide view buttons right above the "A" cell? Sometimes shows when I hide columns. Hi 'Data - Goup/Outline' -- Regards Frank Kabel Frankfurt, Germany Seth wrote: > What is or how do you control, turn on/off the small "1" "2" or "+" > "-" hide/unhide view buttons right above the "A" cell? Sometimes > shows when I hide columns. You can toggle them to hide or display with <Ctrl> <8> Usi...

chart MAcro to change on activecell
Here is the macro below.. The only problem is that the columns change every month. so range (columns)keeps widening.... I have sorted it by selecting range upto column N. so it is provided for all 12 months. But now the "Grand total" column which is always the last column also gets included..(.which shouldnt be included in the range for the chart) Is there a way to modify this macro ? Sub updatechart1() Dim ThechartObj As ChartObject Dim Thechart As Chart Dim Userrow As Long Dim CatTitles As Range Dim SrcRange As Range Dim SourceData As Range If Sheets("summary").Ch...

transparent areas in charts?
How do you create transparent areas in imbedded charts? (for Excel 2003). When I click on "Format Chart area", then "Fill effects", there is a place where you can change the transparency settings, but it is faded out and will not work. Any Ideas? You can make most elements totally transparent. Ignore the transparency button, which is only for decoration. Instead, if you set an element's area to none, the element will be transparent. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech....

Multiple stacked bar charts
I have a project where i have to chart the compliance of three groups over a number of months. So for each data set there is a Compliance#1 stacked upon NonCompliance#1, then Compliance#2 stacked upon NonCompliance#2 and finally, you guessed it, Compliance#3 stacked upon NonCompliance#3. When i design a chart in excel it wants to place everything on top of each other producing one column of six not the required three columns of two for each week in the display. Can anyone point me i the right direction for this one?? Hi you can achieve this effect by having two layers in your x axis the at...

Linking a Chart with graphics to ppt
Dear group, when I create a chart with scatters, which carry graphics like Excel-Block-Arrows and link this to Powerpoint, the graphics are shown there in a very low quality with large pixels. Any ideas how to improve this? Thanks in advance, Holger. ...

printing 2 charts on single page
Using Excel 2k, Win 2k I was trying to print to line charts on a single page to demonstrate the corelation between the two data sets. I would like to print in portait mode with one chart on top of page and one on bottom. Excel only seems to offer putting chart 2 on a separate page or embedding it in chart one. I am not very familiar with excel charting capabilites can anyone suggest a resorce? Thanks ...

Month problem
I have some code where I calculate this month minus 2 month. This goes fine until I get to august. When I use DateSerial to deduct 2 month I get to July, and this makes no sence. I made the following testcode. Sub StrangeMonth() Dim MyDate As Date MyDate = #8/31/2010# MsgBox DatePart("m", DateSerial(Year(MyDate), _ Month(MyDate) - 1, Day(MyDate))) ' MsgBox = 7 MsgBox DatePart("m", DateSerial(Year(MyDate), _ Month(MyDate) - 2, Day(MyDate))) ' MsgBox = 7 MsgBox DatePart(&qu...

shortcut with command line switch probs
Hello, I have a shortcut set up which has the target declared as: "C:\Program Files\Microsoft Office\Office\EXCEL.EXE" / "\\path_to_file\myfile.xlt" This works fine on my PC. Excel 2000 running on Windows XP. Unfortunately it doesnt work on someone elses PC, who asked for m help! They have the same software set up, and access to the same folde path. When they try to run the shortcut, a window opens and closes almos immediately. Help?! : -- soloto ----------------------------------------------------------------------- solotoo's Profile: http://www.excelforum.com/m...

EXCEL 2007 Formula to calculate INTEREST only on a 3 month bridge
I am trying to calculate monthly INTEREST ONLY payment on a short term bridge/swing loan. Assuming an interest rate of 2.75%, paid monthly, Also assuming it will be required for a 3-6 month period, amount approx $500,000. Just switched to Excel 2007 but don't seem to be able to calculate using the formula builder. Not sure if it is compounded daily or monthly. You might want to check out the IPMT function. From the XL help file: IPMT(rate,per,nper,pv,fv,type) Rate is the interest rate per period. Per is the period for which you want to find the interest and must...

Page setup for 8.5x11 calendar w/picture on tabloid paper?
How can I set up a calendar with 8.5x11 (landscape) pages with pictures for each month to print on tabloid (11x17) paper so it can be saddle stiched? I would like the picture on the top half and calendar on the bottom half. I keep ending up with two of the same pictures on one page and two calendars on the next page. Thanks, Bob I assume you are taking this to a professional printer so why not just set each page up as a landscape 8� x 11 and let your printer do the rest? -- JoAnn Paules MVP Microsoft [Publisher] "Bob at 4799" <Bob at 4799@discussions.microsoft.com>...

Help me identify my missing permission (Cannot open public folder) -2147217843 (Maybe Authentication Fails?)
The following snippet of code throws an error number -2147217843. When I googled this error code, I see many references to authentication failed. I am assuming my problem is some kind of permission related problem on the "MyNewFolder" public folder. -- start code --- Dim objFolder As New CDO.Folder Dim f As ADODB.Field 'sURL is like: file://./backofficestorage/mydomain.com/Public Folders/MyNewFolder/ objFolder.DataSource.Open sURL, , adModeReadWrite, adFailIfNotExists --- end code -- I have code that runs before this that actually creates the "MyNewFolder" publ...

Associating Multiple Existing Contacts to an Account, Until CRM 2.x
I have racked my pea-sized brain for endless days for a way to associate multiple existing contacts (or accounts) to an account. My client has accounts that have many contacts (or accounts) involved at different stages of acquiring a new account. The logical method would be to add a custom attribute with lookup functionality into the existing CRM contact or account objects but this is not possible with CRM 1.x. The resulting UI would allow an end user to associate different contacts (or accounts) to an account. Example: Account: ABC - AccountPhysician: (existing account or con...

2 Domains, 1 Exchange Server
Hi, We're trying to go with 2 seperate 2003 domains, but with only one Exchange 2003 Server. What would be needed for the domain that the Exchange server is not in to access e-mail? We've contemplated having those users use OWA, but would really like them to be able to use Outlook 2003. We're a school district and want to segregate the kids domain from the administration domain, but only have funding for one Exchnage server. Any ideas\help will be appreciated. TIA. Hi, This should explain what it requires: http://support.microsoft.com/?id=278888 Leif "tj woo...

RMS 1.2 vs 1.3, plus integrate with Great Plains
1.) I am working on an assessment for upgrading our current RMS software from 1.2 to 1.3. My assessment will take in account the benefits, broken down by store operations (Retail) and the benefits to IT. Also, list risks and potential problems that might be experienced. We currently have 28 stores with 3+ registers per location with another 22 new stores on the schedule over the next 2 years. Are their other in this group of similiar size that have done this upgrade to the new version? 2.) If we do not upgrade the software, can we still integrate the RMS to Great Plains? Would we...

Create Exchange mailbox from command line
I'm writing a script using dsadd and I was wondering if it's possible to create an exchange mailbox from the command line. Donovan Maybe not exactly what you want but it may help: http://www.joeware.net/win/free/tools/exchmbx.htm -- Neil Hobson Exchange MVP For Exchange news, links, and tips, check: http://www.msexchangeblog.com "Donovan Linton" <DonovanLinton@discussions.microsoft.com> wrote in message news:D9C839EF-883D-4E2E-8BE9-57782582F043@microsoft.com... > I'm writing a script using dsadd and I was wondering if it's possible to > create an ...

copy chart from Excel to Powerpoint
I am trying to copy a chart from Excel to Powerpoint, but the button is grayed out and it won't let me copy it. You can convert the chart to a GIF (a picture) with my Excel Objects converter. http://www.geocities.com/excelmarksway "Hoffperson" wrote: > I am trying to copy a chart from Excel to Powerpoint, but the button is > grayed out and it won't let me copy it. ...

Help! charts disappearing, new charts crashing powerpoint
When i open a presentation the charts vanish...i have restarted, they come back. I try to edit a chart, and it crashes powerpoint after a few clicks. What can I do...whole afternoon going crazy! If you are using v 2007 there is a hotfix which is meant to cure some chart crashing problems http://support.microsoft.com/default.aspx?scid=kb;en-us;976479&sd=rss&spid=11264 -- john ATSIGN PPTAlchemy.co.uk Free PPT Hints, Tips and Tutorials http://www.pptalchemy.co.uk/powerpoint_hints_and_tips_tutorials.html "UKExcelgeek" wrote: > When i open a pres...

RPC Over HTTP on Single Server
I have installed Exchange 2003 SP1 on a single server and installed and configured OWA. In following KB 833401, it says to add reg. entries to the \NTDS service. I don't have Active Directory installed on the computer. How do I ensure a proper configuration (see below), when this option is not avail. since I did not install AD, as suggested, on the Exchange 2003 server. "Configure all your global catalogs to use specific ports for RPC over HTTP for directory services" HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\NTDS\Parameters\NSPI interface protocol sequences...

y value vs x
In an xy scatter plot one can choose the type of line connecting the data points (smooth, straight, etc.). Once this is done, Is there a simple way of determining the y value of graph for a specific x value without doing successive approximations with 0 shifts. I'd rather not purchase a graphing program just for this simple task. You could find an equation that fits the data (see trendline) best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "ralph" <ralph@discussions.microsoft.com> wrote in message news:284B39DE-20C6-40CB-AB60-39B...

ACC: How to Display Line Numbers on Subform Records
I found this article on MS' website http://support.microsoft.com/kb/q120913/ but ran into problems when I pasted the code and tried to compile it. It gives a Compile Error: Method or data member not found when it reached the line below: RS.FindFirst "[" & KeyName & "] = " & KeyValue and it highlights "FindFirst". I have a sub-form (child) of a parent form's and I have a variable in the child that the user currently manually increments from 1,2,3 and so forth. Can this not be done w/o the user's intervention? Hi, ...

Text box in Embedded chart is cut off
Hi. I am having a problem with an embedded chart that appears differently in word versus how it appeared originally in Excel. Specifically, the top of the chart (in excel) has a text box that divides the chart (it is a vertical bar graph) into subcategories (labels at the bottom are used to specify what each bar signifies; but these are taken from the associated cell range). Originally, the chart was labeled by a single text box, separated by spaces: Lifetime Use 30-Day Use Heavy Use Antisocial Behavior When this chart is linked in word, the text displays like this (tho&...