2. 3. 4. If you have Excel 2010/13/16, go to the Data tab and locate the Data Analysis option under the Analysis category. If it is not listed there, click the File button, then go to Options. Click on the Add-Ins option and you will see Analysis ToolPak option in the list. Hit the Go button at the bottom. Now select the Analysis Toolpak option and click OK to complete the configuration. In the Data Analysis dialog box, scroll the list box, select Regression, and choose OK. Input Y Range: Point to or enter the reference for the range containing values of the dependent variable ($B$1: $B$16). Include the label above the data. Input X Range: Point to or enter the reference for the range containing values of the two explanatory variables ($C$1: $D$16). Include the labels above the data.

MATLAB: An Introduction with Applications
6th Edition
ISBN:9781119256830
Author:Amos Gilat
Publisher:Amos Gilat
Chapter1: Starting With Matlab
Section: Chapter Questions
Problem 1P
icon
Related questions
Question
2.
3.
4.
5.
6.
7.
8.
9.
10.
If you have Excel 2010/13/16, go to the Data tab and locate the Data Analysis
option under the Analysis category. If it is not listed there, click the File button,
then go to Options. Click on the Add-Ins option and you will see Analysis
ToolPak option in the list. Hit the Go button at the bottom. Now select the
Analysis Toolpak option and click OK to complete the configuration. In the Data
Analysis dialog box, scroll the list box, select Regression, and choose OK.
Input Y Range: Point to or enter the reference for the range containing values of
the dependent variable ($B$1: $B$16). Include the label above the data.
Input X Range: Point to or enter the reference for the range containing values of
the two explanatory variables ($C$1: $D$16). Include the labels above the data.
Labels: Select this box, because the labels at the top of the Input Y Range and
Input X range were included in those ranges.
Constant is Zero: Do not select this box. Select this box only if you want to force
the regression line to pass through the origin (0, 0).
Confidence Level: Select this box and erase 95. Otherwise, Excel automatically
includes 95% confidence intervals twice. (For an additional confidence interval,
select this box and enter the level in the Confidence Level box.)
Output location: Click the Output Range button and point to or type a reference
for the top-left corner cell of a range sixteen columns wide where the summary
output should appear. (For example, $A$20)
Click OK. The summary output appears.
Optional: To change column width so that all summary output labels are visible,
select the cell containing the Adjusted R Square label. Hold down the Control key
while clicking the following cells: Significance F, Coefficients, Standard Error,
and Upper 95%. Go to Home tab. From the Format menu, choose AutoFit
Column Width.
Transcribed Image Text:2. 3. 4. 5. 6. 7. 8. 9. 10. If you have Excel 2010/13/16, go to the Data tab and locate the Data Analysis option under the Analysis category. If it is not listed there, click the File button, then go to Options. Click on the Add-Ins option and you will see Analysis ToolPak option in the list. Hit the Go button at the bottom. Now select the Analysis Toolpak option and click OK to complete the configuration. In the Data Analysis dialog box, scroll the list box, select Regression, and choose OK. Input Y Range: Point to or enter the reference for the range containing values of the dependent variable ($B$1: $B$16). Include the label above the data. Input X Range: Point to or enter the reference for the range containing values of the two explanatory variables ($C$1: $D$16). Include the labels above the data. Labels: Select this box, because the labels at the top of the Input Y Range and Input X range were included in those ranges. Constant is Zero: Do not select this box. Select this box only if you want to force the regression line to pass through the origin (0, 0). Confidence Level: Select this box and erase 95. Otherwise, Excel automatically includes 95% confidence intervals twice. (For an additional confidence interval, select this box and enter the level in the Confidence Level box.) Output location: Click the Output Range button and point to or type a reference for the top-left corner cell of a range sixteen columns wide where the summary output should appear. (For example, $A$20) Click OK. The summary output appears. Optional: To change column width so that all summary output labels are visible, select the cell containing the Adjusted R Square label. Hold down the Control key while clicking the following cells: Significance F, Coefficients, Standard Error, and Upper 95%. Go to Home tab. From the Format menu, choose AutoFit Column Width.
A bank would like to develop a model to predict the total sum of money customers
withdraw from automatic teller machines (ATMs) on a weekend based on the following
model:
Y₁ = B₁ + B₁x₁ + B₂x₂₁ + €₁
where Y = Withdrawal amount ($000)
ATM#
A random sample of 15 ATMs is selected with the following results.
Median
Assessed Value
of Houses ($000)
123456
7
8
9
1.
x₁ = Median assessed value of houses in the vicinity of the ATM ($000)
1 if the ATM is located in a shopping center
10 otherwise
OH23HS
14
X2i
=
Withdrawal
Amount($000)
12.0
9.9
9.1
8.2
12.4
10.4
12.7
8.0
11.5
9.7
11.7
8.6
10.9
9.4
11.2
225
170
153
132
237
187
245
125
215
170
223
147
197
167
210
Location of ATM
0
0
0100
The following steps describe how to use the Regression tool in Microsoft Excel.
Arrange the data in columns: The two (or more) explanatory variables must be in
adjacent columns. Label the data in the top cell of each column (for example,
ATM#, Y, X1, and X2) and enter the data under the corresponding labels. (You
might like to center the data in each column, including the label, by clicking the
appropriate icon on top.)
Transcribed Image Text:A bank would like to develop a model to predict the total sum of money customers withdraw from automatic teller machines (ATMs) on a weekend based on the following model: Y₁ = B₁ + B₁x₁ + B₂x₂₁ + €₁ where Y = Withdrawal amount ($000) ATM# A random sample of 15 ATMs is selected with the following results. Median Assessed Value of Houses ($000) 123456 7 8 9 1. x₁ = Median assessed value of houses in the vicinity of the ATM ($000) 1 if the ATM is located in a shopping center 10 otherwise OH23HS 14 X2i = Withdrawal Amount($000) 12.0 9.9 9.1 8.2 12.4 10.4 12.7 8.0 11.5 9.7 11.7 8.6 10.9 9.4 11.2 225 170 153 132 237 187 245 125 215 170 223 147 197 167 210 Location of ATM 0 0 0100 The following steps describe how to use the Regression tool in Microsoft Excel. Arrange the data in columns: The two (or more) explanatory variables must be in adjacent columns. Label the data in the top cell of each column (for example, ATM#, Y, X1, and X2) and enter the data under the corresponding labels. (You might like to center the data in each column, including the label, by clicking the appropriate icon on top.)
Expert Solution
trending now

Trending now

This is a popular solution!

steps

Step by step

Solved in 4 steps with 2 images

Blurred answer
Similar questions
  • SEE MORE QUESTIONS
Recommended textbooks for you
MATLAB: An Introduction with Applications
MATLAB: An Introduction with Applications
Statistics
ISBN:
9781119256830
Author:
Amos Gilat
Publisher:
John Wiley & Sons Inc
Probability and Statistics for Engineering and th…
Probability and Statistics for Engineering and th…
Statistics
ISBN:
9781305251809
Author:
Jay L. Devore
Publisher:
Cengage Learning
Statistics for The Behavioral Sciences (MindTap C…
Statistics for The Behavioral Sciences (MindTap C…
Statistics
ISBN:
9781305504912
Author:
Frederick J Gravetter, Larry B. Wallnau
Publisher:
Cengage Learning
Elementary Statistics: Picturing the World (7th E…
Elementary Statistics: Picturing the World (7th E…
Statistics
ISBN:
9780134683416
Author:
Ron Larson, Betsy Farber
Publisher:
PEARSON
The Basic Practice of Statistics
The Basic Practice of Statistics
Statistics
ISBN:
9781319042578
Author:
David S. Moore, William I. Notz, Michael A. Fligner
Publisher:
W. H. Freeman
Introduction to the Practice of Statistics
Introduction to the Practice of Statistics
Statistics
ISBN:
9781319013387
Author:
David S. Moore, George P. McCabe, Bruce A. Craig
Publisher:
W. H. Freeman