On May 25, 7:59 am, Gerrie Mostert wrote:
I want to do a scatter graph of a retailer with the
correlation between sales and gross profit.
how do i do it?

This is very basic, but difficult to explain click-by-click if you are
not savvy enough to figure this out for yourself. Also, the click-by-
click instructions vary depending on the version of Excel, which you
neglected to mention. My instructions are for XL2003.

Suppose sales is in D2:D13 and gross profit is in G2:G13. Ideally,
select both columns. One way: select D2:D13, then press and hold the
Ctrl key while you select G2:G13.

Now click the Chart Wizard icon on the toolbar. If it is not there,
click View, then click Chart.

In the first menu of the Chart Wizard, click the XY Scatter chart
type. You can also click a chart subtype. Then click Finish.

If the "curve" (imaginary or actual lines through the data) looks like
it follows a pattern, you might want to also chart a trendline.

Click the chart, point to the data points, right-click, and click Add
Trendline. In the Type tab, select one of linear, logarithm, power or
exponential, whichever you think might best fit the curve. I also
like to select Display Equation and Display R-Squared in the Options
tab. Then click OK.

Do not expect the trendline to fit the data exactly. But you are
looking for a large R-squared.

It is quite possible (likely) that none of the standard trendlines
fits the data well. All that means is: none of the standard
forecasting methods work well with your data. On to Plan B ;-).

By the way, resist the temptation to use a polynomial trendline, even
if fits the data exactly. An n-order polynomial trendline should fit n
+1 or fewer data points exactly. But it will usually be a poor
predictor of past or future data points, unless you have some reason
to believe your data has the kind of geometry.