Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Statistical Analysis

Posted on 2010-11-29
3
Medium Priority
?
725 Views
Last Modified: 2012-05-10
Hello all,
Can someone show me how to set up this problem by looking at the data attached and below questions.

The statistical analysis of the data involves hypothesis testing and multiple-regression analysis.
Analyses that you'll want to do are:
1.Test the hypothesis that mean price does not depend on whether the car is a convertible. (Interpret your answer in nontechnical terms for "Tom".) Then, perform the same hypothesis for transmission type, presence or absence of air conditioning, GT model or not, and private versus dealer ownership.
2.Perform a hypothesis test that mean selling price does not vary with color.
3.      Find a reasonable multiple regression model of PRICE on the other variables available in the data set. (Which variables are significant, which  
0
Comment
Question by:joesack99
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 81

Accepted Solution

by:
byundt earned 2000 total points
ID: 34233148
You should be aware that the statistical functions in Excel were reworked with Excel 2010 and 2011. They had a checkered reputation in the academic community due to occasional erroneous results that were easy for the casual user to encounter. They had previously been reworked for Excel 2003, but this latest revision is an attempt to answer the criticism for once and for all.

I assume that this is a homework type of assignment, and so will not post my version of the workbook to avoid getting you in trouble with the instructor.

When answering the first question, I used the TTEST function in an array formula that tested whether the attribute was present or not:
=TTEST(IF(B$2:B$36=0,$A$2:$A$36,""),IF(B$2:B$36<>0,$A$2:$A$36,""),2,3)
The TTEST function returns the probability that the two sets of data come from the same population. A number close to 0 means that they are likely to come from different populations. Variables like Color, Age and Mileage aren't really suitable for this type of test, so don't be alarmed by the error value that is returned. Note: you could have sorted the data by column B and entered the two resulting ranges separately in a regular formula. The array formula gives the same answer and saves time by letting you copy it across.

Array-entering a formula is a little tricky:
1) Click in the formula bar and paste the formula
2) Hold the Control and Shift keys down
3) Hit the Enter key, then release all three keys
Excel should reward you with curly braces { } surrounding the formula. You may see a #VALUE! error value if you didn't follow the directions correctly.

I also tried using the RSQ function to return the R-squared value (square of Pearson's correlation coefficient) for the correlation of price with each of the variables. A value close to 0 means that there is little correlation.
=RSQ($A$2:$A$36,B$2:B$36)

The combination of TTEST and RSQ led me to eliminate two of the variables--your judgment & textbook may suggest retaining a different number of variables. I then rearranged the data with the excluded variables off on the right. I could now use LINEST to return the regression equation for the remaining variables. If you exclude two variables, then select a five row x eight column range of cells and array-enter a formula like:
=LINEST(A2:A36,B2:H36,TRUE,TRUE)

The on-line help tells you how to interpret the results. It is worth noting that the constant is at the far right of the top row, and the coefficients for the variables are in reverse order (coefficient for column B appears next to the constant). I like to look at the R-squared for the overall correlation to see how good the fit is; you'll find this in the third row on the left.

Brad
0
 

Author Comment

by:joesack99
ID: 34233537
Brad,
Thanks for your response. I have Excel 2007 with Megastat plugin. Can you please the same step if I use Megatstat?
0
 
LVL 81

Expert Comment

by:byundt
ID: 34233762
I am not familiar with the Megastat addin. Sorry.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question