Correlation and Regression on Excel?


a. Draw scatter plot for the variables
b. Compute the value of the correlation coefficient.
c. State the hyptothesis.

12. A medical researcher wishes to see if there is a relationship between prescription drugs and identical dosages that are prescribed for humans and for animals. The prices are shown. Is there a relationship between the prices?

Prices for humans (x) : .67/.64/1.20/.51/.87/.74/.50/1.22
Prices for animals (y) : .13/.18/.42/.25/.57/.57/.49/1.28

This is one of the problems i have to do in Excel, and I don't know how to do please?:yup:
if you have an installer of your microsoft office then you can use PH Stat to do the work..

or use this command: Tools > Data Analysis > Correlation

or for...
a. you can use the graph option and select the corresponding values given
b. and c. use the formula of correlation coefficient and rules in constructing hypothesis

i hope this helps..


New Member
Click the 'fx=' button situated just left of formula bar, then under 'select catagory' list click on 'Statistical'. Check the formulae you require.
For plotting data you may use "Graph" freeware, which has different nice utilities and Excel data can be copied into it.
Two separate tests are designed to measure a student's ability to solve problems. Several students are randomly selected to take both tests and the results are given below.

Test A -
Test B -

(Find the value of the linear correlation coefficient r.)
(Assuming a .05 significance leve, find the critical value of r.)
(Using the first two parts, what do you conclude about the correlation between the two variables?)
(Find the value of m, the slope of the regression line.)
(Find the value of b, the y-intercept of the regression line.)
(Give the equation of the regression line.)
(What is the standard error of estimate.)
(What is the coef. of determination?)
What percentage of the total variation can be explained by the regression line?)
(Given a student scored 60 on Test A. Predict her score on Test B.)

Help on this one?
Last edited:


New Member
Your questions require long answers but they are quite simple. Go through a 12th std. text book. Be patient, you shall be able to answer all within an hour.
Copy the data in two columns of Excel. In Statistical functions (as mentioned in my first thread) you get the answers (for that matter use a scientific calculator to get the results with not much time).

Standard error is nothing but standard deviation by formula.
Coeff of Determination (CD) is the square of correln. coeff. and
100CD= the % of variance explained by linear regression.
Last edited: