Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Correlation analysis | Excel Worksheet Functions | |||
regression analysis | Excel Discussion (Misc queries) | |||
Using Regression Analysis? | New Users to Excel | |||
In Regression Analysis, How do you get a Correlation Matrix to be. | Excel Discussion (Misc queries) |