Free Microsoft Excel 2013 Quick Reference

two scatter plots with two trendlines on one chart

I know how to do a scatter plot and create a trend line for it.
Straightforward graphing function.

However, I have *two* data sets (with different number of data points)
that I would like to compare. Obviously, I can do a chart for each
and put them side by side.

Problem: I would like to display a scatter plot for each set with a
trend line for each on the *same* chart using Excel 2000.

Thanks.

--
=================================================
Do you like wine? Do you live in South Florida?
Visit the MIAMI WINE TASTERS group at
http://groups.yahoo.com/group/miamiWINE
=================================================


I know how to do a scatter plot and create a trend line for it.
Straightforward graphing function.

However, I have *two* data sets (with different number of data points)
that I would like to compare. Obviously, I can do a chart for each
and put them side by side.

Problem: I would like to display a scatter plot for each set with a
trend line for each on the *same* chart using Excel 2000.

Thanks.

--
=================================================
Do you like wine? Do you live in South Florida?
Visit the MIAMI WINE TASTERS group at
http://groups.yahoo.com/group/miamiWINE
=================================================

i am trying to create a scatter plot in excel with one set of x vaules, and 2 sets of y values ( both sets of y value data uses the same variable). essentially, how can i get two scatter plots on one graph ? is it possible ? do you guys know what i mean ?

thanks,

Malcolm347

I've recorded a macro to create a scatter plot with trendlines in one worksheet (run 1). I'd like to use the same macro on other worksheet but I'm not sure what I need to edit in my code to do that. I've tried changing 'run 1' to 'activesheet' and that does not work. How do I generalize this code?
My code:


	VB:
	
ActiveSheet.Shapes.AddChart.Select 
ActiveChart.ChartType = xlXYScatter 
ActiveChart.SeriesCollection.NewSeries 
ActiveChart.SeriesCollection(1).Name = "='run 1'!$B$1" 
ActiveChart.SeriesCollection(1).XValues = "='run 1'!$B$3:$B$74" 
ActiveChart.SeriesCollection(1).Values = "='run 1'!$C$3:$C$74" 
ActiveChart.SeriesCollection(1).Trendlines.Add 
ActiveChart.SeriesCollection(1).Trendlines(1).Select 
Selection.Intercept = 0 
Selection.DisplayEquation = True 
Selection.DisplayRSquared = True 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi,

I have a list of 50 names or so, and two variables attached to each name. I need to see, on ONE chart, a scatter plot to show the direct relationship between these two variables. However, I need to see the names attached to each data point, or else the legend which is 50 names long gets confusing. Furthermore, many of these names share one or both of the same variables! I still need to see a plot for each individual name on the graph though.

Any ideas? I was thinking about trying to create some sort of matrix, but I don't know how to do that either.

Your help is MUCH appreciated!

I have two data sets from the Vostok Ice Core data (http://www.ncdc.noaa.gov/paleo/iceco...pica_data.html) that I would like to plot on one chart for comparison. They are for two different variables (temperature and CO2) and therefore have different units (deg C and ppm), and different value ranges (-11 to +6 deg C, and 170 to 300 ppm). It seems best to create dual-scale y axes, but I'm also not sure how to do that.

Additionally, the values for the x-axis are in years, but they are not in regular intervals (the further back in time, the more widely they are spaced). And the year values are not the same for both (e.g. Temp may have data at years 102 and 109, but CO2 have data at years 98 and 113). How can I plot the two data sets on one chart so that the values for years are linear along the x axis?

It seems like it should be easy, but I can't seem to wrangle Excel into doing what I want. Any help much appreciated.

I currently have Excel from 2002 installed, but I think I can get work to upgrade it to a newer version if that helps.

I'm having a bit of trouble creating a scatter plot with data in the following format. In one table I have a series of date columns with the time I reached a given progress marker each data represented in rows. In another column I have the distance between those markers. I would like to setup a scatter plot with the distance and time on the axes, additionally connecting the data points for each day.

excel.png

Ignore the fact that in this graph there are two separate tables for there and back again. I would like to do this without repeating the distance data over and over again, but wouldn't know how to do it even with that compromise.

I have one chart on the worksheet which I am going to use to cycle different data through.

I've set this case statement up to change the trendlines, but now that I've added more, how can I change them all without repeating the seriescollection over and over six times?

Also, for the Parabola chart, changing the trendline doesn't seem to be as straightforward. What I have it the code below doesn't work? Any suggestions?

Private Sub
Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Set rng = Target.Parent.Range("B4")
    If Target.Count > 1 Then Exit Sub
    If Intersect(Target, rng) Is Nothing Then Exit Sub
    ActiveSheet.ChartObjects("Chart 1").Activate
    With ActiveChart.SeriesCollection(2).Trendlines(1)
        With ActiveChart.SeriesCollection(3).Trendlines(1)
            Select Case Target.Value
            Case "Linear"
                .Type = xlLinear
                .Type = xlLinear
            Case "Power"
                .Type = xlPower
                .Type = xlPower
            Case "Parabola"
                .Type = xlPolynomial
                With Selection
                    .Type = xlPolynomial
                    .Order = 2
                End With
            Case "Ratio"
                .Type = xlLogarithmic
                .Type = xlLogarithmic
            End Select
        End With
    End With
    Application.Goto reference:=Range("A1")
End Sub


Ok, I give up. In Excel 2007, how do I plot two data series having common
x-values? When I go to Select Data Source and Add a series, I have to
specify both x-values and y-values. If I add a second data series with the
same x-values as the first, then the x-axis labels are just 0,1,2,..., not
the x-values I specified. I do get a scatter plot with two data series, but
the x-axis is not correctly labelled.

This was no problem in prior versions of Excel, but like everything else in
the "easier to use" Office 2007, it is much harder to figure out.

Hi all, I began working in a clinical research lab today, and the experimenters have put me straight to work. I am trying to create a marked scatter plot (with no line, this is their personal request) consisting of data obtained from 3 different test groups, with the clinical value plotted on the Y-axis and the month (multiple month long experiment) plotted on the X-axis. Thus, I have say, 25 points all under a single given X value, e.g. month 1. I was wondering if there's a way to "spread out" the values so that you can see each individual point, thereby stretching the points out along the X-axis so they aren't clustered so close together. Below is a screen shot of what the chart looks like, my apologies for not being able to articulate what I want well, to say I'm an excel novice would be an understatement.

http://imageshack.us/photo/my-images...0618at353.png/

Hello There!

I'm new to this board as well as excel so I'm hoping someone has some suggestions!

I've created a scatter plot with absorbance on the y-axis and concentration on the x-axis with a trendline from an experiment I did. From a separate experiment I have 3 absorbance values. My goal is to calculate the x-axis values by finding out exactly where along the x-axis these 3 absorbance values hit the trendline. Is there any way I can do this??

Any suggestions would be greatly appreciated!! Thank you!

Hi,
I'm attaching a file with some data that feeds a XY scatter plot with lines connecting the data points. The X axis shows dates, which are interpolated from the data. I'd like the axis to have markers at

1-Jun, 1-Jul, 1-Aug, etc

but I have not found a way to do this. I can play with the axis scale settings and start at 1-Jun, but I cannot set the Major unit to be 1 month. I can set it to 30 days, but our calendar system being what it is, that gets me

1-Jun, 1-Jul, 31-Jul, 30-Aug

and so on.

Any ideas how I can see the 1st of each month?????

thanks for reading

Teylyn

Hi,

This has been troubling me for nights.

I have data columns of X, Y1, Y2, Y3, Y4...
I used XY scatter to plot them all on one chart, but would like to have a filter on the plotter to select the desired series for comparison, eg, Y1, Y4, and Y5 only. Is there a method of doing this?

I tried using pivot table without joy because it doesn't support XY scatter.
I tried grouping the data sheet, but this gets very messy and is not selectable from the chart.

Please Help!!!

Can any one help with a way to plot multiple histograms on one plot?
I have data as folow:
bin1 frequency1 bin2 frequency2 bin3 frequency3
0 5 0 10 0 20
2 0 1 5 3 9
5 36 3 2 7 25
.. . . . . .
.. . . . . .

Thank you

I want to do a "Scatter plot with data points connected by lines" (under "X-Y
(Scatter Charts)") but with a 3-D visual effect like can be done in "3-D
Line" under Line charts.

this would allow a user to create something like a 3-d model of any object.
they allow the option for line charts, and i don't see the same thing for
scatter charts. seems like it would be very helpful, especially for what i
am doing, but for lots of other people too. any suggestions?

dan

I have an xy-scatter plot with 2 series, say Series1 and Series2. The data
is arranged in 4 columns, Series1-x-values, Series1-y-values,
Series2-x-values and Series2-y-values. I would like to draw a line between
the Series 1 and Series 2 xy-coordinate for each row in my data. Does anyone
have good tips on doing this? I would appreciate any help.
Thanks,
Anne

Hello,

I was wondering if any might tell me if it is possible to have two sets of option buttons on one excel sheet. I know in userforms you must put them in a frame but that option is not available on the actual excel sheets.

Thanks
Saylor

I am trying to plot in excel a scatter plot with mulitple xy settings..All
the x setting are time. I am wondering if there is a way with in excel to
select the columns and then plot them accordingly

Thanks
--
Kat

I'm having trouble when i'm making my scatter plot. My X and Y values are ranges like 0 - 1 and 4.5 - 5.0.
Is it even possible to make a scatter plot with points like that?
Thanks!

I am posting this message for two reasons: (1) partially to confirm the existence of an Excel 2007 VBA bug that appeared after applying Office 2007 SP1, but (2) mostly to highlight the existence of such a bug so that others are aware of it. (I'm pretty sure it's a real bug, and not just an issue with my PC.)

When there are two or more series on a scatter plot, it appears that a call to Series.XValues(n) will return "n" rather than the value of the nth point. For example, if there is a Series object named "mySrs", then Code:
 will incorrectly return "5", rather than the x-value of the fifth point in the series. This seems to occur only when there
are two or more series; if there is only one series, Series.XValues(n) returns the proper value.

Here are the steps to recreate the issue in Excel 2007 with SP1:

* Create a new spreadsheet
* Populate two columns of data for the X and Y values for the first series in the scatter plot. Make sure that the x values are not equally-spaced. (I.e., don't use "1, 2, 3, 4, 5..." for the x values, but instead use someting like "1, 2.3, 4, 4.8, 5.2...")
* Create the scatter plot
* Run a macro to determine the XValues for each point in the series (an example is provided below)

You should notice that--at this point--everything works as expected. Continuing on...

* Populate two additional columns of data for the second series in the scatter plot.
* Add the second series to the scatter plot
* Run the same macro to determine the XValues for each point in the series

At this point you should see that the XValues are simply (and incorrectly) "1, 2, 3, 4..."

Here is the sample macro code that I used to discover the bug:
Code:
Sub CheckChart()
      Dim myCht As Chart
      Dim mySrs As Series
      Dim Npts As Integer
 
      Set myCht = ActiveSheet.ChartObjects(1).Chart
      Set mySrs = myCht.SeriesCollection(1)
      Npts = mySrs.Points.Count
 
      Dim NumPts As Integer
      For NumPts = 1 To Npts
        Debug.Print "Point " & NumPts & ": XValue=" & mySrs.XValues(NumPts) & ", Value=" & mySrs.Values(NumPts)
      Next
 
      Debug.Print vbCrLf & "=================================" & vbCrLf
 
End Sub
The take-away is that if you are using VBA code that depends on the XValues of a series (such as Jon Peltier's excellent line and fill effects procedures), your code will not work properly.

It would be helpful if someone could validate this bug, but—as I indicated above—I'm pretty sure it's real.

I did submit feedback to Microsoft (here), but I'm not confident that it will result in a fix anytime soon.

Hope this helps you avoid hours of frustration and troubleshooting!

Cross post here

I am posting this message for two reasons: (1) partially to confirm the existence of an Excel 2007 VBA bug that appeared after applying Office 2007 SP1, but (2) mostly to highlight the existence of such a bug so that others are aware of it. (I'm pretty sure it's a real bug, and not just an issue with my PC.)

When there are two or more series on a scatter plot, it appears that a call to Series.XValues(n) will return "n" rather than the value of the nth point. For example, if there is a Series object named "mySrs", then
 will incorrectly return "5", rather than the x-value of the fifth point in the series.  This seems to occur only
when there are two or more series; if there is only one series, Series.XValues(n) returns the proper value.

Here are the steps to recreate the issue in Excel 2007 with SP1:
* Create a new spreadsheet
* Populate two columns of data for the X and Y values for the first series in the scatter plot. Make sure that the x values are not equally-spaced. (I.e., don't use "1, 2, 3, 4, 5..." for the x values, but instead use someting like "1, 2.3, 4, 4.8, 5.2...")
* Create the scatter plot
* Run a macro to determine the XValues for each point in the series (an example is provided below)

You should notice that--at this point--everything works as expected. Continuing on...
* Populate two additional columns of data for the second series in the scatter plot.
* Add the second series to the scatter plot
* Run the same macro to determine the XValues for each point in the series

At this point you should see that the XValues are simply (and incorrectly) "1, 2, 3, 4..."

Here is the sample macro code that I used to discover the bug:
Sub CheckChart()
      Dim myCht As Chart
      Dim mySrs As Series
      Dim Npts As Integer
      
      Set myCht = ActiveSheet.ChartObjects(1).Chart
      Set mySrs = myCht.SeriesCollection(1)
      Npts = mySrs.Points.Count
      
      Dim NumPts As Integer
      For NumPts = 1 To Npts
        Debug.Print "Point " & NumPts & ": XValue=" & mySrs.XValues(NumPts) & ",
Value=" & mySrs.Values(NumPts)
      Next
      
      Debug.Print vbCrLf & "=================================" & vbCrLf
      
End Sub
The take-away is that if you are using VBA code that depends on the XValues of a series (such as Jon Peltier's excellent line and fill effects procedures), your code will not work properly.

It would be helpful if someone could validate this bug, but—as I indicated above—I'm pretty sure it's real.

I did submit feedback to Microsoft (here), but I'm not confident that it will result in a fix anytime soon.

Hope this helps you avoid hours of frustration and troubleshooting!

Hi,
I am having a real problem with Excel 2007 (and PowerPoint 2007) when trying
to manipulate very basic scatter plots (two series, smoothed lines, 8000 data
points per series). When I try selecting one of the series, Excel goes off in
a huff for about 20 or 30 minutes (I am not exaggerating! It "...is not
responding" for that period and loses the frame it normally has, replacing
this with a standard Window title bar superimposed over the normal Excel 2007
one, until it gives back focus) and then repeatedly becomes non responsive
when trying to manipulate any of the properties (line colour, line style
etc). I have tried repairing Office 2007 and have even tried completely
uninstalling and then reinstalling the program set (but have not "cleaned"
the registry of Office entries after the uninstall and therefore still have
all my preferences on reinstallation) but that has not changed anything. If I
do not try to manipulate this type of chart, Excel 2007, and the other Office
programs behave well. I have saved the file to an xls file and can happily
open and adjust the chart to my heart’s content with no issues using Excel
2002 (running on a lower spec machine).
Is this an issue with Excel or perhaps a compatibility problem (I am running
Office 2007 on a Toshiba Tecra M5 laptop with a 2+ GHz Core Duo and1Gb RAM,
with Windows XP SP2)? Is there any way I can tell what might be / is causing
the problem? Are there any simple solutions I can try? Or do I need to
rebuild my laptop installation from scratch (something I am very loathed to
do!)?
Any hl would be very welcome... if I hadn't lost my hair already, I would be
pulling it out by now!
Best wishes, Boris.

Hi,

I've been struggling to represent data from a spreadsheet of mine for a while now and have not found the answer through searching. Sadly the data I have is sensitive so I can't post the spreadsheet but hopefully I can explain it.

I record survey data from thousands of people. This data records their issues with a service along with demographic data and location they use the service. This is searched through several pivot tables.

What I need to display is the results for a specific location against the grand total so that I can see what one location suffers from a particular issue more than the grand total. (It is all expressed in percentage terms)

The best I have managed so far is to place two pivot tables side by side. One shows every response broken down by issue. The other shows the responses for each issue only at a certain location. Ideally I'd like to be able to plot these results on the same graph but I can't seem to do so.

Hopefully this is clear. If not please ask and I can provide further info.

Many thanks,

Alan

I have two sets of data for a lightbulb that are to be plotted by time included below. I plotted the first dataset as a scatter plot of % _intensity as a function of time, then I used an x-axis error bar with custom error using the duration_hours column in order to indicate when the time the lightbulb got turned on and at what % of it's intensity. I also have another dataset which indicates when the lightbulb flickers momentarily and when the when it flickers and stays at a higher intensity for a duration of time. I've created two plots in excel for each data. I'm trying to overlay the two graphs on each other so I can compare the datas. Is there a way to do this in excel? Better yet, is there a way I can plot these two dataset in one excel graph?

-Thanks

Model Begin Duration_hours %_intensity A 7/3/2007 4:34 0.595 1 A 7/5/2007 20:12 0.238 0.2 A 7/16/2007 6:36 7.307 0.7 A 8/5/2007 15:22 0.183 0.4 A 9/3/2007 11:17 0.15 0 A 9/25/2007 0:48 1.13 1 A 9/28/2007 6:42 6.806 0
Model date_time flicked flicked_open A 5/2/2007 13:37 0 A 5/2/2007 16:43 0 A 7/3/2007 19:23 0 A 7/10/2007 5:57 0 A 7/18/2007 15:11 0 A 7/18/2007 15:11 0 A 7/19/2007 10:12 0 A 7/22/2007 1:59 0 A 7/24/2007 16:36 0 A 8/10/2007 14:32 0 A 8/13/2007 18:04 0 A 9/10/2007 4:36 0 A 9/10/2007 4:36 0 A 9/16/2007 19:25 0 A 11/12/2007 6:56 0 A 11/12/2007 7:07 0 A 12/24/2007 0:39 0

i want to create a scatter plot (plot a against b ) with fitted straight line
i come up with these codes, but there are two errors occured , how can i solve these ???

Dim ser As Series
Dim trend As Trendline
With Charts.Add

        With .SeriesCollection.NewSeries
            .Values = a                       (run-time error '1004': unable to set the Values property of the Series class)
            .XValues = b
        End With
        .ChartType = xlXYScatter
End With
Set ser = ActiveChart.SeriesCollection(1)
Set trend = ser.Trendlines.Add(Type:=x1Linear, DisplayEquation:=True)                (run-time error '1004': unable to get
the Add property of the Trendlines class)