The measure of correlation is called the correlation coefficient<\/strong>. If you have two sets of variable data, you can calculate the Pearson product-moment correlation coefficient (r<\/strong>) using the CORREL function<\/a> in Google Sheets.<\/p>\n Note: If you are looking for more functions, we have a huge guide of 100+ Functions & Formulas for Google Sheets & Excel Basics<\/a><\/p>\n The Pearson product-moment correlation coefficient (also referred to as Pearson’s\u00a0r, or simply r) measures the strength of the\u00a0linear<\/strong>\u00a0association between two variables.<\/p>\n The correlation coefficient r<\/strong>\u00a0has a value of between \u22121 and 1.<\/p>\n The three graphs below will help you get a better understanding of correlation.<\/p>\n [\/et_pb_text][et_pb_text admin_label=”Connections T – Automate between spreadsheets – Horizontal” module_class=”sheetgo-post-no-shadow-img md2-contained-button-light vertical-banner-container” _builder_version=”4.27.4″ _module_preset=”default” background_color=”#f2f7ff” max_width=”700px” module_alignment=”center” max_height=”300px” custom_margin=”20px|0px|20px|0px|true|true” custom_padding=”25px|25px|25px|25px|true|true” sticky_limit_bottom=”section” border_radii=”on|20px|20px|20px|20px” border_width_all=”1px” border_color_all=”#d9e7ff” saved_tabs=”all” global_colors_info=”{}” global_module=”255443″ theme_builder_area=”post_content”]<\/p>\n Automate data transfers between spreadsheets Find out how<\/a><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n This positively correlated data has a correlation coefficient of about 0.9 \u2013 close to the maximum of 1. If the correlation were truly 1, then the data would be in a perfectly straight line.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/11\/Correl-image-1.png” alt=”correl-function-google-sheets-graph-1″ title_text=”Correl image 1″ _builder_version=”4.23.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The negatively correlated data has a coefficient of about -0.9, which means that the data is well-correlated, but in a negative relationship. So in this case, y decreases as x increases.<\/p>\n In both the positive and negative correlations, it’s possible to draw a \u201cline of best fit\u201d through the data points. This line would give a fairly accurate representation of the data.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/11\/Correl-image-2.png” alt=”correl-function-google-sheets-graph-2″ title_text=”Correl image 2″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n In the graph below, you can see non-correlated data with a correlation coefficient of about 0.04. This indicates that there is no meaningful relationship between the x and y variables.<\/p>\n You would have a hard time drawing any sort of line of best fit through these data points!<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/11\/Correl-image-3.png” alt=”correl-function-google-sheets-graph-3″ title_text=”Correl image 3″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n If you want to read more about the Pearson product-moment correlation coefficient and how to calculate it,\u00a0here\u2019s the link<\/a>.<\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.16″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =CORREL(data_y, data_x)<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n As you can see, there are two options for inputting the dataset. You can use a reference range, or input the data directly into the function.<\/p>\n Generally, using a reference range is a better option. That’s because your data is probably already in the spreadsheet. Using a reference range means you won\u2019t have to re-type it in the function itself.<\/p>\n If you do choose to type the dataset directly into the formula, make sure you enclose both the x and y data in curly brackets {}. If you\u2019re referencing the data in a range of cells, you don’t need to use curly brackets.<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n In the syntax, you can see that you need two sets of data (x and y) for the CORREL function to return a value.<\/p>\n I\u2019ll now show you some practical examples in Google Sheets.<\/p>\n The parameters for the CORREL function accept the input values in two different ways.<\/p>\n You can choose to input the direct numeric datasets using curly brackets that indicate ranges.<\/p>\n As you can see, this is what I’ve done in the example in row 2.\u00a0 Here I have typed in the formula =Correl({8.4,8,1.8,7.2,0.9},{14,20,6,12,3}).<\/p>\n Alternatively, you can simply use range references. You can see that in the example in row 3, where I\u2019ve typed =Correl(A2:A11,B2:B11).<\/p>\n In both examples, you get a correlation coefficient that is positive and close to 1. While interpretations vary depending on the application and the nature of the data, the first correlation coefficient of 0.91 indicates that the data is closely correlated. So, when x<\/strong> changes, y<\/strong> changes in a very similar way.<\/p>\n The second correlation coefficient of 0.75 indicates that the data is less correlated than the first dataset \u2014 but still fairly correlated.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/11\/Correl-image-4.png” alt=”correl-function-google-sheets-4″ title_text=”Correl image 4″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Now, you\u2019ll see how the function behaves if the x and y data are in a linear relationship (y = 10x + 5).<\/p>\n In the following image, I\u2019ve added data that fits this relationship. Since this is a direct dependency, the function returns 1 for the correlation coefficient.<\/p>\n Remember, a value of 1 indicates that the data is perfectly correlated.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/11\/Correl-image-5.png” alt=”correl-function-google-sheets-5″ title_text=”Correl image 5″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n What happens when I try an equation with a negative slope (y = -5x+10)?<\/p>\n In this case, the correlation coefficient should be -1, because this is a negative dependency. So when x<\/strong> increases, y<\/strong> decreases by a proportional amount.<\/p>\n You can see in the snapshot below that this is indeed the case.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2020\/11\/Correl-image-6.png” alt=”correl-function-google-sheets-6″ title_text=”Correl image 6″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n That’s the Sheetgo guide on how to calculate a correlation in Google Sheets \u2014 thanks for reading! Looking for more advanced Google Sheets tips? Check out our other posts such as how to import Google Sheets data from one sheet to another<\/a> or how to use QUERY with Importrange.<\/a><\/p>\n Share it with your colleagues, friends, and fellow spreadsheet users via the social media buttons on the left.<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.22.2″ background_color=”#f9f9ff” custom_margin=”40px||40px||false|false” custom_padding=”15px|25px|15px|25px|true|true” border_width_left=”3px” border_color_left=”#808e95″ saved_tabs=”all” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Editor\u2019s note<\/strong>: This is a revised version of a previous post that has been updated for accuracy and comprehensiveness.<\/em><\/p>\n [\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n","protected":false},"excerpt":{"rendered":" When you’re carrying out data analysis, statistical measures help you to understand underlying trends. One of the most common statistical metrics is correlation. It’s used to gauge the extent of the relationship, or dependence, of two variables. The measure of correlation is called the correlation coefficient. If you have two sets of variable data, you […]<\/p>\n","protected":false},"author":40,"featured_media":27682,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_et_pb_use_builder":"on","_et_pb_old_content":"Analyzing data entails going through various statistical measures to understand the underlying nature and trends. Probably, one of the most commonly used statistical methods is Correlation. We use it to gauge the extent of relationship, dependence or closeness of two variables with respect to each other. And the metric used to calculate this dependence is called correlation coefficient. Given the two sets of variable data, we can calculate the Pearson product-moment correlation coefficient (r<\/strong>) using the CORREL<\/a> formula in Google Sheets.\n\nIt is worth noting that the correlation coefficient r<\/strong> ranges from \u22121 to 1. When r<\/strong> = 1, it indicates the two variables are in a perfect linear relationship. And when r<\/strong> = 0, it implies that there is no correlation between the variables. A value of \u22121 implies that the variables are in a negative relationship i.e. if x increases, then the value of y decreases and vice versa. If you are interested in reading through some literature as to how we usually calculate the Pearson product-moment correlation coefficient, here\u2019s the link<\/a> to it.\nMeasuring correlation in Google Sheets<\/h3>\n
\n
![\"\"](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/09\/run-automatically-connect-sheet-icons.webp\"){kind=icon}
<\/p>\n![\"39\"](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/09\/Recommended-for-Google-Workspace-badge.webp\"){kind=icon}
<\/p>\n
<\/span><\/p>\nPositive correlation<\/h4>\n
Negative correlation<\/h4>\n
No correlation<\/h4>\n
Syntax<\/h3>\n
\n
How to use the correlation function in Google Sheets<\/h3>\n
Correlation using brackets<\/h4>\n
Correlation using range references<\/h4>\n
Positive relationship<\/h4>\n
Negative relationship<\/h4>\n
Did you like this post?<\/h4>\n
Syntax<\/h3>\nCORREL(data_y, data_x)<\/strong>\n
\n \t
Usage: CORREL formula in Google Sheets<\/h3>\nHaving gone through the syntax, we now understand that we need two sets of data for the formula to return a value. Now, let us go ahead and try our hands practically on Google Sheets application itself. Consider the screenshot below.\n\n
![\"CORREL](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2018\/06\/CORREL-formula-Illustration-Frame-1.png\")
\n\nThe parameters accept the input values in two different ways. We can either choose to input the direct numeric datasets using curly braces that indicate ranges (first example). Or we can simply use range references (second example).\n\nNow, let us see how the formula behaves if x and y data were in a linear relationship (y = 10x + 5). Please see the following image. Since this is a direct dependency, the formula returns 1 for output.\n\n![\"CORREL](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2018\/06\/CORREL-formula-Illustration-Frame-2.png\")
\n\nWhat if we tried an equation with a negative slope (y = -5x+10)? We should see -1 in our result because this is a negative dependency. And, the snapshot below confirms it.\n\n![\"CORREL](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2018\/06\/CORREL-formula-Illustration-Frame-3.png\")
","_et_gb_content_width":"","footnotes":""},"categories":[54],"tags":[39,28],"class_list":["post-8835","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-google-sheets-formulas","tag-connections-t","tag-spreadsheets"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/posts\/8835","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/users\/40"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/comments?post=8835"}],"version-history":[{"count":0,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/posts\/8835\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/media\/27682"}],"wp:attachment":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/media?parent=8835"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/categories?post=8835"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/tags?post=8835"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}