Is it possible to plot this kind of data in a (line) chart w/ preferred axis? How?

How would I plot this data in a line chart? 

(MS Excel 03) 

(Samples below should be viewed in non-proportional font or formatting
will not be clear)


Raw data (below):

     A          B          C         D          E
 0 @ 1000   0 @ 1000   0 @ 1060    0 @ 865   0 @ 1200
10 @ 1700  10 @ 1700   2 @ 1340   2 @ 1325   2 @ 1460
       na         na  17 @ 2400  11 @ 2400  12 @ 2200
26 @ 5000  26 @ 5000  24 @ 4800  18 @ 4200  20 @ 5000

The catch is I need to have the 2nd # be the X axis (0...6000).


I can put the data in the spreadsheet something like this and tell it
to ignore empty values (below):

         A       B   (etc)
  +-----------------------
0 |    1000    1060
1 |		
2 |            1340
3 |		
4 |		
5 |		
6 |		
7 |		
8 |		
9 |		
10|   1700	
11|		
12|		
13|		
14|		
15|		
16|		
17|           2400
18|		
(etc)

Or I can put the data in the sheet in another format...?


But ultimately I need to have the line chart look something like this
(below):

etc...
10|
 9|
 8|
 7|
 6|
 5|
 4|
 3|
 2|
 1|
 0+------------------------>
    1K  2K  3K  4K  5K  6K

And I can't figure out how to get Excel to do this chart from this
data! Surely this is possible? Appreciate any help or tips!
0
pgtr
9/14/2004 2:52:13 AM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
666 Views

Similar Articles

[PageSpeed] 11

Hi Pgtr,

> How would I plot this data in a line chart? 
> 
> (MS Excel 03) 
> 
> (Samples below should be viewed in non-proportional font or formatting
> will not be clear)
> 
> Raw data (below):
> 
>      A          B          C         D          E
>  0 @ 1000   0 @ 1000   0 @ 1060    0 @ 865   0 @ 1200
> 10 @ 1700  10 @ 1700   2 @ 1340   2 @ 1325   2 @ 1460
>        na         na  17 @ 2400  11 @ 2400  12 @ 2200
> 26 @ 5000  26 @ 5000  24 @ 4800  18 @ 4200  20 @ 5000
> 
> The catch is I need to have the 2nd # be the X axis (0...6000).

I'm not sure I'm interpreting the data correctly, but is this five data 
series, each with their own x and y values:

 A: (1000,0),(1700,10),...,(5000,26)
 B: (1000,0),(1700,10),...,(5000,26)
 C: (1060,0),(1340,2),...
 etc.?

 If so, you can arrange your data like the following:
 
    A      B      C      D      E      F    etc.
 1  SeriesA       SeriesB       SeriesC
 2  X      Y      X      Y      X      Y
 3  1000   0      1000   0      1060   0
 4  1700   10     1700   10     1340   2
 5
 ...

 In other words, but the X and Y data for each series in their own 
column, with the X values to the left of the Y's.
 
 Now create an XY chart using just the values for SeriesA (columns A and 
B).  Then select the two columns for SeriesB and Edit > Copy.  Select 
the chart and Edit > Paste Special.  In that dialog, choose to paste as 
a new series, with X values in the first column, but don't replace the 
existing X values.  Repeat for the rest of the series to get the chart I 
think you want <g>.
 
Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.ie


0
9/14/2004 8:47:00 AM
On Tue, 14 Sep 2004 09:47:00 +0100, Stephen Bullen <stephen@bmsltd.ie>
wrote:

>Hi Pgtr,
>
>I'm not sure I'm interpreting the data correctly, but is this five data 
>series, each with their own x and y values:

Yes, precisely. But I must have the larger #s 0...5000 to be in the X
axis.

> A: (1000,0),(1700,10),...,(5000,26)
> B: (1000,0),(1700,10),...,(5000,26)
> C: (1060,0),(1340,2),...
> etc.?

Yes.

> If so, you can arrange your data like the following:
> 
>    A      B      C      D      E      F    etc.
> 1  SeriesA       SeriesB       SeriesC
> 2  X      Y      X      Y      X      Y
> 3  1000   0      1000   0      1060   0
> 4  1700   10     1700   10     1340   2
> 5
> ...

Interesting. OK got it.

> In other words, but the X and Y data for each series in their own 
>column, with the X values to the left of the Y's.


OK. Let's just work w/ a single series pairing for simplicity...

   A     B
Series 'X'
1060     8
1340    10
2400    25
4800    32

Desired graph format:
 (B)
etc
25|          *
24|
23|
22|
21|
20|
19|
18|
17|
16|
15|
14|
13|
12|
11|
10|     *
 9|   
 8| *
 7|
 6|
 5|
 4|
 3|
 2|
 1|
 0+------------------------>(A)
    1K  2K  3K  4K  5K  6K

THen if I can get that to work I'll expand it to include all the data.

> Now create an XY chart using just the values for SeriesA (columns A and 
>B).

But when I graph... all I can do is create 2 SEPARATE INDEPENDENT
plots of the 'paired' data. Eg. one plot uses 1060...1340...2400...etc
as Y data as a function of 1,2,3... and the other uses 8,10,25... also
as a function of 1,2,3. E.g. it's forcing both 'paired' columns into
SEPARATE and unrelated Y values. They aren't 'connected' as one being
the function of another... (in the data above, B is a function of A is
in A(B)). 

In the graph: Y is still 0...5000 and X is simply 1,2,3,4. X should be
1000...2000... up to 6000 or so. And Y should be 0 to 26.

>Then select the two columns for SeriesB and Edit > Copy.  Select 
>the chart and Edit > Paste Special.  In that dialog, choose to paste as 
>a new series, with X values in the first column, but don't replace the 
>existing X values.

OK. All this seems to accomplish is add a 3rd series to the 2 series
already shown (see above) when I really just want a SINGLE line for a
given series.

>Repeat for the rest of the series to get the chart I 
>think you want <g>.

Interesting approach about separating the data out in this form but I
still seem stuck. Any add'l suggestions or did I take any mis-steps
above?

Thanks very much for taking the time to respond!

0
pgtr
9/14/2004 7:40:19 PM
Hi Pgtr,

> But when I graph... all I can do is create 2 SEPARATE INDEPENDENT
> plots of the 'paired' data. Eg. one plot uses 
1060...1340...2400...etc
> as Y data as a function of 1,2,3... and the other uses 8,10,25... 
also
> as a function of 1,2,3. E.g. it's forcing both 'paired' columns into
> SEPARATE and unrelated Y values.

OK:

(a) Make sure you select an XY (Scatter) chart, not a Line chart as the 
chart type.  I get what you're seeing if I choose the Line chart type, 
but not if I choose XY (Scatter).

(b) In Step 2 of the Chart Wizard, click on the Series tab and check 
the 
ranges that Excel is using for each of your series.  Correct them if 
they're wrong.  The 'X Values' should point to the range containing the 
big numbers (1-5000), while the 'Y Values' should point to the range 
containing the small numbers.

Regards

Stephen Bullen



0
9/15/2004 12:36:04 AM
On Wed, 15 Sep 2004 01:36:04 +0100, Stephen Bullen <stephen@bmsltd.ie>
wrote:
>OK:
>
>(a) Make sure you select an XY (Scatter) chart, not a Line chart as the 
>chart type.  I get what you're seeing if I choose the Line chart type, 
>but not if I choose XY (Scatter).

DOH! That was it! SCATTER CHART - thank you VERY MUCH it is now
working exactly the way I want it too. This is shaping up to be a
perfect image for an article I'm working on.
0
pgtr
9/15/2004 1:05:14 AM
Reply:

Similar Artilces:

having trouble creating chart to show monthly totals
Hi I'm using office XP. I keep a single spreadsheet were I record orders received. Its a very simple sheet with one order per row. In col B I place the date the order was received (in the format DD/MM/YY) I'd like to make a chart (pie or column) which displays the total number of orders per month so that I can see at a glance when were busy and quieter periods. I've tried using the wizard but all I ever get is a mess and not what I expect to see at all. I suspect I may need to separate my data by month? But is there a way for Excel to work that out for itself from a sin...

Is it possible
I would like to know is it possible to make million dollar in the internet, if you have any suggestions, write on my forumhttp://isitpossible.truemillion.comMark...

Trendline and multiple data series
I have a scattergraph with four data series. Is there a way of inserting a trendline which covers all four series? Thanks in anticpation Create a helper column that combines all the data series ... Jerry Blackcat wrote: > I have a scattergraph with four data series. Is there a way of inserting a > trendline which covers all four series? > > Thanks in anticpation ...

How do I create a dual axes column chart in Excel
Hi...I am trying to create a dual axes column chart in MS Excel 2003. But the moment I add the secondary axis the bars are getting overlapped. Alok, This example should help: http://www.pdbook.com/index.php/excel/two_column_series_with_two_axes/ -- Regards, John Mansfield http://www.pdbook.com "Alok" wrote: > Hi...I am trying to create a dual axes column chart in MS Excel 2003. But the > moment I add the secondary axis the bars are getting overlapped. ...

Personal Folders in OWA
Hello, I have just enabled OWA on our Exchange 2003 SP2 through ISA Server 2004. Is there any way to access personal folders in OWA? Thanks in advance. If by personal folders you mean .pst files, the answer is NO! Sorry. C C wrote: > Hello, > > I have just enabled OWA on our Exchange 2003 SP2 > through ISA Server 2004. Is there any way to > access personal folders in OWA? > > Thanks in advance. > > "C C" <someone@atsbcglobal.net> wrote: >I have just enabled OWA on our Exchange 2003 SP2 >through ISA Server 2004. Is there any way...

Editing XML data relation line
I am working on a program where I am retrieving data from an Access database through one common dataset. I have already put the database connection component on the form as well as the two data adapters. Here is where I am having problems. I double click on the .xsd file and then go to the schema menu and select add|new relationship. My table names are Employee and Payroll. Employee ID is a primary key in the Employee table and a foreign key in the Payroll table. I need to define a DataRelation link with Employee ID. The problem is that when the parent table is set to Employee and the ch...

Missing Data in Spreadsheet Contained in a Disk Image
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I used Migration Assistant to copy all my applications and files to my new iMac i7. When I attempt to open a vital password protected Disk Image, the .dmg opens OK but the Excel file contained within doesn't have any data in it. Taking a copy of the .dmg from a backup disk results in the same thing. <br><br> What's really peculiar 'though is that if I right click the file before actually opening it, using Quick Look, it shows all the data is there. But the file still won't open the Ex...

Downloading Statements Directly Not Possible
I have Money 2002 installed on an Win XP Pro SP1 machine. Something happened and I probably did it, that causes the statement downloads not to go directly to the account. Rather they want to be saved in a file and then opened into Money. I then have to delete the download. I have tried several things in order to fix this: 1. I verified the file associations of OFC, OFX were correct. They were. 2. Lowered all my security settings in IE. Even put banks into Trusted site zone. No go. 3. Made new accounts. Didn't help. 4. My IE cache is large (500MB) and regularly cleaned out. 5. I made s...

Radar Charting problems, Can I change...
Hi people I have 2 queries relating to Radar charts in Excel (Office XP/2002). Can I change the angle/start position of the first plotted point? I always starts at 12 o'clock but I want it to start at 9 o'clock. I ca re-order the data to achieve this effect but the data, to the viewer is not in a logical sequence if I do that. Then also, can I change the direction of the plotting from clockwise t anti-clockwise? Thanks in advance. Stell -- Message posted from http://www.ExcelForum.com Stella - You can't do these things (unless someone smarter than me knows how), but you c...

is this possible? reload a form that is based on a query with new query that is defined in the form.
I have a basic form. The form is based on a query, so when I open the form, a pop comes up where I enter in what I want to query. So instead of 30,0000 records, I get 1-10 records. on the form, I have a text box. I want to be able to enter in what I want to query next and afterupdate have the recordset/form reload with that query instead of my original qeury. I figure this has to be done with VB. I am just starting to learn some VB but I am lost. Any info would be great, thank you. It does. You need to apply the new query or table name, or the sql string to the forms recordsource, then...

Charts & Hidden Data
Hi, If I hide the row/columns containing the source data of a chart the respective series disappears from the chart. Is there any way to hide chart source data without affecting the chart? Many thanks, Jason In XL2003 (and before) with chart selected: Tools | Options |Chart and uncheck the box "Plot visible cells only" In XL 2007: Follow this path: click the chart; use Chart Tools|Design|Selected Data; look for button labelled "Hidden and Empty Cells"; complete dialog box as needed. best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from ema...

listing cells that have external data link
I bet this has been asked before... Is there a way or a tool to list all cells that are linked to external data sources? Using Excel 2002 Rex Maybe you could use the builtin edit|Find. Search for .xls (or [ or ] Then click the find all button. You'll see a list of all those cells that contain that value--and if you're not using them as plain old text, you should see a list of links. T-Rex wrote: > > I bet this has been asked before... Is there a way or a tool to list all > cells that are linked to external data sources? > Using Excel 2002 > Rex -- Dave Peter...

line spacing #2
How do I reset line spacing in Outlook 2003? My version insists on inserting a double line feed every time I press "Enter". You are using a stationary that uses double lines. Try a different stationary or none at all "crossfire" wrote: > How do I reset line spacing in Outlook 2003? > My version insists on inserting a double line feed every time I press "Enter". > "crossfire" wrote: > How do I reset line spacing in Outlook 2003? > My version insists on inserting a double line feed every time I press "Enter". I appreciat...

Time and motion chart
I am looking for help in creating a �time and motion� chart for up to people and each person has 4 activities that they perform throughou their day. For argument sake, each person is required to perform the followin activities. Stack boxes Pack boxes Unload truck Load truck I would like to put together a time and motion type of chart that show each persons activity by time of day. Each person performs al activities throughout their day at different times. There maybe tw people unloading the truck while one person stacks boxes and the othe is loading the truck for example. Each person c...

Finding the end of Data
I have a data set that has a sorted column filled with either numbers or "#N/A" (which are all found above the numbers) I want to select the rows that have "#N/A" in them. How to I find the last row, please? Jim Berglund Maybe it's not important to find the last row. If you don't have any other errors in that column and these errors are constants (not formulas), you could do the equivalent of: Selecting the column Then hit F5 (edit|goto)|special|constants|and select only errors In code, it would look like: Option Explicit Sub testme() ...

2007 line chart, can't make some gridlines heavier than others
Hi, I had a nice line chart in 2003 with two lines, one for Planned and one for Actual. Across the bottom axis I had years, e.g. 2006, 2007, etc. These years were major gridlines formatted in heavy black and ran vertically. Up the left vertical axis was percent. The reports come in quarterly, therefore, between each yearly heavy gridline, there were 4 quarterly gridlines. So every 4th gridline was heavy black (representing the start of each year), and in between were 3 quarterly gridlines that were light black. The difference between the heavy annual lines with the 3 ligh...

How many and what kinds of reports can MS CRM generate by default?
Hi, can anyone provide such a list? I am new to this package and evaluating its features. Thanks. Microsoft CRM V3 comes with 20 built in reports. These are using SRS or SQL reporting services. You can using visual studio.net to create any additional reports you need. The only limit is your imagination and your report writing skills. Download the CRM sdk form www.microsoft.com/downloads which has a reporting section. ======================= John O'Donnell Microsoft CRM MVP http://codegallery.gotdotnet.com/crm "phw" <phw@discussions.microsoft.com> wrote in message ...

Possible to detect changes to an XmlDocument object?
If I instantiate an XmlDocument object, the load a file through the Load method, is there a way, later on in execution, to easily determine if the XmlDocument object has changed from the original version loaded? I'd like to be able to check a condition to see if the document in memory has been editted (i.e., nodes added, attributes changed, etc.) so that my user can decide whether or not to commit changes to the disk file before moving to another file. Will I have to resort to re-loading the file, and comparing OuterXml properties? Gabe Gabe Covert wrote: > I'd like t...

Export Access Data Project Queries to old Access .mdb file
I want to copy Access Data Project queries from a project into a new .mdb file. What is the best way to do this? In the new .mdb go to the menu bar, click file, Get external data, and Import. Select the orignal .mdb file and then the items. >-----Original Message----- >I want to copy Access Data Project queries from a project into a new .mdb >file. What is the best way to do this? >. > ...

Data Consolidation
Hi Team, Any idea about the Data consolidation in excel . I have multiple excel sheets and want to consolidate it without losing the data. other than RDB data merge . Thank you, Shivaprasad hi Shivaprasad, If those files have all the same schema, you can do it with a VBA macro using mso. -- isabelle Le 2011-10-20 21:47, shivaprasad . shetty a �crit : > Hi Team, > > Any idea about the Data consolidation in excel . I have multiple excel > sheets and want to consolidate it without losing the data. other than > RDB data merge . > > Thank you, > Shivaprasad ...

My Bar Chart Is Missing
I have inserted a bar chart and I have used one of PowerPoint's built in templates. I saved the presentation and now when I open it up the chart outline opens up, the Chart Tools contextual tabs open up, I can view the data, but the slide does not show the bar chart? Any ideas? Your help is greatly appreciated!!!! (B^>)-]=[ ...

Is it possible to compare a string and a field name
I have the following tables: Membership, Subscriptions, OfficeUseOnly. The Office Table contains a Year, Year To and From [financial year] and the other fields are the 5 membership types (regular, student etc) and these 5 fields contain the different amount each type of member has to pay. I want to create a form whereby on entering the financial year and the membership type the correct amount is automatically inserted into the 'Amount' field: FinancialYear - in current form = OfficeUseOnly SubsFinYear and then if MemberType = 'Student' find Student field ...

Grouping data
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) I want to group data: <br> all transactions that took place in time intervals (per hour) <br><br>How can I display the total number of all transactions per time interval? <br><br>Thank you for your response! It depends on the nature of the data & how you have it arranged. The first 2 options that come to mind is the Subtotals feature found in the Data menu as well as the use of a Pivot Table. See the following topics in Excel Help: Add up totals for sales data Analyze your data with P...

sizing pie chart category data label
I would appreciate advice on re-sizing pie chart category data labels. When I click on the label, I get a box around the text, just like other microsoft applications, but when I click and drag on the points, the text label box does not re-size...it stays the same size. Any suggestions? Thank you. I think it auto-adjusts to suit the font-size and text that you have in there. Try right-clicking on the label, then you usually have access to other features. Hope this helps. Pete On Mar 9, 4:41 pm, susanh...@ou.edu wrote: > I would appreciate advice on re-sizing pie chart category data labe...

Update Sub form as user enters data in main form
I have a mainform that uses an unbound combo box to populate a subform. This works great. This will display all of the existing data for my contracts. If however they want to add more contracts, they enter the data on the main form and after continueing on to the next record and selecting that company from the combo box, they will see that their contract has been added to the list. I would like to be able to show the user the data they are entering into the main form is also going directly into the subform as a new record without the need of a command button or advancing a record. At one p...