Hey there! Sign in to join this conversationNew here? Join for free
    • Thread Starter
    Offline

    0
    ReputationRep:
    Hello. Is here someone who likes simple exercises in Excel? I'm doing my coursework and I have problems. I just need some help.

    The correlation and regression analyses are based on quarterly data
    obtained from the International Passenger Survey1 on UK all Visits Abroad (UVA), Expenditure (Ex), Overseas all Visits to the UK (OVU) and Earnings (Er) from 2005Q1 to 2016Q2. The data can be found in an excel file on WebLearn labelled as “ukvisits.xls”.

    Your task is the following:
    1. Using the ‘Data Analysis’ excel facility, show the summary statistics for each variable (UVA, Ex, OVU and Er).

    2. Construct scatter diagrams and calculate the correlation between the
    two variables (UK visits and expenditure; Overseas visits and earnings).

    3. Determine two equations for the simple linear regression line of the scatter diagrams in question 2 above (two SLR equations are required).

    4. Show how the simple linear regression equations can be used to predict expenditure and earnings in the context of the data (One prediction for each case).
    Offline

    14
    ReputationRep:
    Hi, firstly brace yourself slightly - not all that "simple" that you like!

    There are two ways of doing this in Excel: simpler way (still slightly involved!) and complex way.

    I will, of course, show you the simpler way - I might be known as Sheldon, but I am nice, too!! )

    You will first need to glance at the data to see of there is a trend i.e. if there is any correlation between the two variables in each of the scenarios, and therefore whether a best straight line through the points will make sense or a curved line. (I do not know the A level syllabus, but I would have thought it would be rude (in you youngsters' lingo haha!) if it was a curve.

    First look for Data Analysis command in a group called Analysis under Data tab. If t is not there, do [A] 1-5 below. Other wise proceed to Part [B]

    [A]
    1.Click the File tab, and then click Options.

    2.Click Add-Ins, and then in the Manage box, select Excel Add-ins.

    3.Click Go.

    4.In the Add-Ins available box, select the Analysis ToolPak check box, and then click OK.

    5.After you load the Analysis ToolPak, the Data Analysis command is available in the Analysis group on the Data tab.
    B] (For Q1)
    1. Click Data Analysis command and in the dialog box tht opens select Descriptve Statistics

    2. Select the data range and Click OK.

    [C] For Q2

    1. Scatter graph: Insert > Charts > Scatter
    2. Correlation: Data > Analysis > Data Analysis
    In dialog box select correlation OK Select data > OK

    [D] (!3)
    Use the two datasets separately.

    1. Select first two columns:
    In a new empty cell, type the formula "= INTERCEPT(second column, first column) e. g. INTERCEPT(C2:C45, B2:B45) 2. Then do with same data, e.g. =SLOPE(C2:C45, B2:B45)
    Fom your maths equation for straight line is y = mx + c
    In the above , use the answer for INTERCEPT as c
    Use SLOPE as m
    You have your equation.
    3. Do the same for the second dataset : - gives you second equation.
    QUESTION 4 IN A SECOND - PHEW I need a cuppa!!!








    • Thread Starter
    Offline

    0
    ReputationRep:
    Thank you so much for your help. I'm really grateful. I've done diagrams and statistic but I stuck on something, I don't know why INTERCEPT is (C2:C45, B2:B45).



    1. Select first two columns:
    In a new empty cell, type the formula "= INTERCEPT(second column, first column) e. g. INTERCEPT(C2:C45, B2:B45) 2. Then do with same data, e.g. =SLOPE(C2:C45, B2:B45)
    Fom your maths equation for straight line is y = mx + c
    In the above , use the answer for INTERCEPT as c
    Use SLOPE as m
    You have your equation.
    3. Do the same for the second dataset : - gives you second equation.
    QUESTION 4 IN A SECOND - PHEW I need a cuppa!!!
    Offline

    14
    ReputationRep:
    Hi beatris,

    Let me explain:-

    The INTERCEPT function (same with SLOPE function) uses the format: =INTERCEPT(y range, x range); that is, the function name is INTERCEPT and it is a built-in feature of Excel that needs the arguments (arguments means sort of values) first your dependent variable range (y axis values or range) then a comma, then your independent variable range (x axis values or x range). (This is just the way Excel functions have been created whereby each function needs certain value groups or ranges for Excel to work out that particular function) - the same arguments are necessary for the SLOPE function, whereas the FORECAST function requires an extra argument beforehand i.e. the x value for which you want to predict the y value, in your case, the UVA value for which you want to predict the Ex value [in the first dataset, AND the OVU value for which you want to predict the Er value [in the 2nd dataset]).

    In the formula "=INTERCEPT(C2:C45, B2:B45)", the C2 to C45 range is just an imaginary example (I have randomly chosen to illustrate the principle) of a range of y axis (dependent) variable values [C2 to C45] - you will use the range (whole of it) of the values in Ex as this is the y range (because these are the values dependent on the UVA values (which will be your x range)); this latter = UVA values will be the column range you enter in place of B2:B45 in my example.

    Similarly, in the 2nd dataset, you will use your range of Er values in place of the C2 to C45 in my example AND you will use the range of OVU values in place of B2 to B45 in my example.

    I chose the columns C and B respectively, for the y and x ranges, because it is very likely that your dataset has the Ex values to the right of the UVA values (1st set) and the Er values to the right of the OVU values (2nd set), just like column C is to the right of column B.

    I hope this clarifies things for you.

    Mukesh
    Offline

    14
    ReputationRep:
    "whereas the FORECAST function requires an extra argument beforehand i.e. the x value for which you want to predict the y value, in your case, the UVA value for which you want to predict the Ex value [in the first dataset, AND the OVU value for which you want to predict the Er value [in the 2nd dataset])."

    Hi again, the above, copied from the previous post, ill not mean much to you as one of my posts (for Q4) failed to upload apparently.


    [E] Q4Use the FORECAST function to work out what the predicted value of [in 1st dataset] Ex might be for a value of UVA chosen by you that is larger than the values given to you, and to predict value of Er for a value of OVU chosn by you (2nd dataset)

    The FORECAST function needs arguments [in the round brackets) "=FORECAST(x, y range, x range)" where x is the value of UVA for which you want to predict the Expense [1st dataset] AND where x is the value of OVU for which you want to predict the Er value [2nd dataset].

    I would be surprised if you have not got a headache by now - soooorrry!!
 
 
 
  • See more of what you like on The Student Room

    You can personalise what you see on TSR. Tell us a little about yourself to get started.

  • Poll
    What newspaper do you read/prefer?
  • See more of what you like on The Student Room

    You can personalise what you see on TSR. Tell us a little about yourself to get started.

  • The Student Room, Get Revising and Marked by Teachers are trading names of The Student Room Group Ltd.

    Register Number: 04666380 (England and Wales), VAT No. 806 8067 22 Registered Office: International House, Queens Road, Brighton, BN1 3XE

    Quick reply
    Reputation gems: You get these gems as you gain rep from other members for making good contributions and giving helpful advice.