Excel – Plot a scatter, a line and a quadatic function in the same graph in Excel

microsoft excelmicrosoft-excel-2007

I have two groups of data. One group has three points: (0.5, 2), (1.5, 3) and (4, 5). The other group has two points: (0, 1) and (3, 4). I want to plot the first group as a scatter plot, and the second as a straight line segment, and a quadratic function $y=x^2$ over $x \in [0,5]$ in the same plot. I wonder how to do that in Excel?

I know how to plot the first group as a scatter plot. First create a column with data: 0.5, 1.5 and 4, and another column with data: 2, 3 and 5; then select the two columns, and click "Insert -> Scatter" and choose a type of scatter plot.

But I don't how to plot the other two, i.e. a line segment and a quadratic function.

I also don't know how to plot them in the same graph as the scatter plot. If I plot the second group of data, it will be plotted in another graph. How can I plot it in the same plot as the first group of data?

Excel 2007.

Best Answer

For the line segment, you just choose to plot as a scatter plot, but use a line to connect the points.

For the quadratic, you will need to compute a number of points on your curve and then plot them also as a scatter plot, and again, use a line to connect the points from your minimum X value up to your maximum X value.

Update (for Excel 2007 ... probably similar for other versions, but this is what I have in front of me):

To add an additional plot to your chart:

Right click on an empty place in your chart and select "Select Data"
Select "Add" from the Series list
Optionally, name the series in the Series name textbox ... it will be given a default name if you don't provide one.
Click on the Series X data textbox, and select the X values for your scatter plot
Click on the Series Y data textbox, and select the Y values corresponding to the X values you just selected
Press "OK"

Related Solutions

Excel – Finding Y with X on Excel scatter plot/trend line

If you know about the model, which is linear in your case, then you can use the following formula to calculate Y values corresponding the X values you want. Let's say you want to calculate Y where X=3.5. Then you should use:

=3.5*LINEST(B2:B6,A2:A6)

where B2:B6 is the range for Y values, and A2:A6 is the range for X values.

Other functions that may be useful for you:

LOGEST(known_y's,known_x's,const,stats)

_{In regression analysis, calculates an exponential curve that fits your data and returns an array of values that describes the curve. Because this function returns an array of values, it must be entered as an array formula.}

GROWTH(known_y's,known_x's,new_x's,const)

_{Calculates predicted exponential growth by using existing data. GROWTH returns the y-values for a series of new x-values that you specify by using existing x-values and y-values. You can also use the GROWTH worksheet function to fit an exponential curve to existing x-values and y-values.}

FORECAST(x,known_y's,known_x's)

_{Calculates, or predicts, a future value by using existing values. The predicted value is a y-value for a given x-value. The known values are existing x-values and y-values, and the new value is predicted by using linear regression. You can use this function to predict future sales, inventory requirements, or consumer trends.}

Excel – Plot scatter graph in Excel graph with 3 variables in 2D

You can do this in Excel!!! In fact, there are at least two ways to create a chart like this:

Scatter Plot with Date Variable

The first method is easiest, but requires a free add-in (that every Excel user probably should have anyway), Rob Bovey's XY Chart Labeler.

Set up your data (as described).
Add your data as a single Scatter (XY) data series.
Use XY Chart Labeler (its awesome) to Add Labels to your data series, using the Date data column (formatted to just days [dd] in this sample), located above your data point.
Format everything to your taste.

Otherwise, you can do it this way.

Set up your data (as described).
Add your data as one Scatter (XY) data series for every row in your data (one per day, in this sample). So, if you have 31 days, you'll have 31 series.
Add your data labels using the data points name, located above your data point.
Format everything to your taste (including all 31 data series and their labels).

I'd strongly recommend the first method, or using VBA to expedite the second.

Best Answer

Related Solutions

Excel – Finding Y with X on Excel scatter plot/trend line

Excel – Plot scatter graph in Excel graph with 3 variables in 2D

Related Question