Correlation or Regression Analysis
 

Hi,

My manager wants an analysis of these variables (He said correlation but I
think he meant regression, but am not sure). I have no idea on how to
proceed. What he wants is a graph that has conversion on the y axis, with
the conversion points being what is graphed. I don't know what goes on the x
axis. He said that he wants to know what stores are lower (conversion wise)
and what factors would cause conversion to increase.
(Conversion is calculated as transactions divided by traffic).

Any help would be very much appreciated. Thank you.
store Sales Conversion ATV Traffic Transactions
2 9,837 24.31% $43.14 938 228
3 12,035 27.24% $40.39 1,094 298
5 9,371 22.90% $40.39 1,013 232
11 12,747 23.79% $40.59 1,320 314
12 7,336 21.84% $46.72 719 157
13 4,515 22.39% $33.44 603 135

Correlation or Regression Analysis
 

Hi,

You may be better in charting but here's my effor
Click on the graph icon
Select Line and go for a simple line graph (Top left sample)
Click the 'series ' tab and then ADD
Click the symbol on the right of the NAME box and select the cell with
"Conversion"
Click the symbol again
Click the values box and select the cells with the conversion factors in
Click the X axis box and select the cells with the Store numbers in

NEXT
You can now do all sorts of fancy things to the graph
When you finished click Finish and you now have a graph of store conversions

Mike


"les8" wrote:


Hi,

My manager wants an analysis of these variables (He said correlation but I
think he meant regression, but am not sure). I have no idea on how to
proceed. What he wants is a graph that has conversion on the y axis, with
the conversion points being what is graphed. I don't know what goes on the x
axis. He said that he wants to know what stores are lower (conversion wise)
and what factors would cause conversion to increase.
(Conversion is calculated as transactions divided by traffic).

Any help would be very much appreciated. Thank you.
store Sales Conversion ATV Traffic Transactions
2 9,837 24.31% $43.14 938 228
3 12,035 27.24% $40.39 1,094 298
5 9,371 22.90% $40.39 1,013 232
11 12,747 23.79% $40.59 1,320 314
12 7,336 21.84% $46.72 719 157
13 4,515 22.39% $33.44 603 135

Correlation or Regression Analysis
 

Hi,

This is a regression problem, not really a charting issue. You will want
to use the LINEST function. The help system shows and example similar to
yours:

Specifically Example 3:

Example 3 Multiple Linear Regression

Suppose a commercial developer is considering purchasing a group of small
office buildings in an established business district.

The developer can use multiple linear regression analysis to estimate the
value of an office building in a given area based on the following variables.

Variable Refers to the
y Assessed value of the office building
x1 Floor space in square feet
x2 Number of offices
x3 Number of entrances
x4 Age of the office building in years

This example assumes that a straight-line relationship exists between each
independent variable (x1, x2, x3, and x4) and the dependent variable (y), the
value of office buildings in the area.

The developer randomly chooses a sample of 11 office buildings from a
possible 1,500 office buildings and obtains the following data. "Half an
entrance" means an entrance for deliveries only.

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

Create a blank workbook or worksheet.
Select the example in the Help topic.
Note Do not select the row or column headers.



Selecting an example from Help
Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return
the results, press CTRL+` (grave accent), or on the Tools menu, point to
Formula Auditing, and then click Formula Auditing Mode.

1
2
3
4
5
6
7
8
9
10
11
12
A B C D E
Floor space (x1) Offices (x2) Entrances (x3) Age (x4) Assessed value (y)
2310 2 2 20 142,000
2333 2 2 12 144,000
2356 3 1.5 33 151,000
2379 3 2 43 150,000
2402 2 3 53 139,000
2425 4 2 23 169,000
2448 2 1.5 99 126,000
2471 2 2 34 142,900
2494 3 3 23 163,000
2517 4 4 55 169,000
2540 2 3 22 149,000
Formula
=LINEST(E2:E12,A2:D12,TRUE,TRUE)


Note The formula in the example must be entered as an array formula.
After copying the example to a blank worksheet, select the range A14:E18
starting with the formula cell. Press F2, and then press CTRL+SHIFT+ENTER. If
the formula is not entered as an array formula, the single result is
-234.2371645.

When entered as an array, the following regression statistics are returned.
Use this key to identify the statistic you want.



The multiple regression equation, y = m1*x1 + m2*x2 + m3*x3 + m4*x4 + b, can
now be obtained using the values from row 14:

y = 27.64*x1 + 12,530*x2 + 2,553*x3 - 234.24*x4 + 52,318

The developer can now estimate the assessed value of an office building in
the same area that has 2,500 square feet, three offices, and two entrances
and is 25 years old, by using the following equation:

y = 27.64*2500 + 12530*3 + 2553*2 - 234.24*25 + 52318 = $158,261

Or you can copy the following table to cell A21 of the example workbook.

Floor space (x1) Offices (x2) Entrances (x3) Age (x4) Assessed value (y)
2500 3 2 25 =D14*A22 + C14*B22 + B14*C22 + A14*D22 + E14

You can also use the TREND function to calculate this value.


This is not an area one just jumps into, good luck.

If this helps, please click the Yes button.


--
Thanks,
Shane Devenshire


"les8" wrote:


Hi,

My manager wants an analysis of these variables (He said correlation but I
think he meant regression, but am not sure). I have no idea on how to
proceed. What he wants is a graph that has conversion on the y axis, with
the conversion points being what is graphed. I don't know what goes on the x
axis. He said that he wants to know what stores are lower (conversion wise)
and what factors would cause conversion to increase.
(Conversion is calculated as transactions divided by traffic).

Any help would be very much appreciated. Thank you.
store Sales Conversion ATV Traffic Transactions
2 9,837 24.31% $43.14 938 228
3 12,035 27.24% $40.39 1,094 298
5 9,371 22.90% $40.39 1,013 232
11 12,747 23.79% $40.59 1,320 314
12 7,336 21.84% $46.72 719 157
13 4,515 22.39% $33.44 603 135

Correlation or Regression Analysis
 

Thanks for the help in graphing. I appreciate it. I also need help in doing
the regression or correlation analysis. I have no idea on how to do it.

Thanks.

"Mike H" wrote:

Hi,

You may be better in charting but here's my effor
Click on the graph icon
Select Line and go for a simple line graph (Top left sample)
Click the 'series ' tab and then ADD
Click the symbol on the right of the NAME box and select the cell with
"Conversion"
Click the symbol again
Click the values box and select the cells with the conversion factors in
Click the X axis box and select the cells with the Store numbers in

NEXT
You can now do all sorts of fancy things to the graph
When you finished click Finish and you now have a graph of store conversions

Mike


"les8" wrote:


Hi,

My manager wants an analysis of these variables (He said correlation but I
think he meant regression, but am not sure). I have no idea on how to
proceed. What he wants is a graph that has conversion on the y axis, with
the conversion points being what is graphed. I don't know what goes on the x
axis. He said that he wants to know what stores are lower (conversion wise)
and what factors would cause conversion to increase.
(Conversion is calculated as transactions divided by traffic).

Any help would be very much appreciated. Thank you.
store Sales Conversion ATV Traffic Transactions
2 9,837 24.31% $43.14 938 228
3 12,035 27.24% $40.39 1,094 298
5 9,371 22.90% $40.39 1,013 232
11 12,747 23.79% $40.59 1,320 314
12 7,336 21.84% $46.72 719 157
13 4,515 22.39% $33.44 603 135