Question

I am currently doing research and I need to find the correlation between "HOURS OF SLEEP" and "LPI SCORES" I want a table to look as below how would I achieve this on Excel.

In short I want to find out what the average score someone gets when they get a certain amount of sleep.

I have all the data in a spreadsheet but cannot get the table right.

SLEEP HOURS                               LPI SCORES

4                                                      ??

5

6

7

8

9

10

11

12

Answer 1

>> In order to find a correlation between two or more columns in excel follow this step

>> Go to Data tab>>Analysis section click on Data analysis>>select correlation>> select your data>> tick on label if your selected data contain the first row as label>> select the output range where you want to get output>> you will see a correlation table where you wanted the output

>> For the 2nd part of your question, you need to perform a regression analysis in order to find the average LPI score based on number of sleep hours

>> Go the same data analysis section>> select LPI score as y range>> select Sleep hours column as x level>> tick appropriate section what you wanted>> click ok>> you will get a table and plenty of graph>> your intercept and coefficient of the regression equation is in the table itself

>> suppose you get like this

>> score = intercept + SleepsCoefficnt x Hours of sleep

just put hourse of sleep to get average score