Excel for Begginers

Thiago has a degree in Production Engineering, a graduate course in statistics and a degree in administration from the Federal University of Juiz de Fora (UFJF). Black Belt in Lean Six Sigma, worked with methodologies for cost reduction and process optimization at Votorantim Metais, subsequently joining MRS Logística as a trainee, where he held positions of manager and specialist in continuous improvement. With Microsoft Office Specialist (MOS) certification and ISO 9001 Auditor Lead Advisor, attended several companies in consulting projects, in addition to training and lectures related to Lean Six Sigma, Career Entrepreneurship in nationally renowned congresses such as ENEGEP (National Meeting of Production Engineering) and international as International Six Sigma Brazil congress. In the academic environment, he works as a professor of undergraduate and graduate courses in the areas of management and entrepreneurship. Thiago had the opportunity to participate in projects of several segments. Founder of the Voitto Group, he was selected in the Promessas Endeavor Program, having the opportunity to receive valuable mentoring to accelerate his business. He is currently a business mentor and dedicates himself to the executive front of Voitto, carrying with his team the vision of being the best online school of management in Brazil.

Founder of Voitto Group, currently a business mentor and dedicated to the executive front of Voitto, carrying with his team the vision of being the best online business school in Brazil.

See how easy it is to do a simple linear regression in Excel and analyze if the results obtained are consistent!

How to do a simple linear regression in Excel

I&#39;m sure in the sentence &quot;simple linear regression in excel&quot;, the second and third words can scare, and the most reassuring part is the simple word, is not it?This word both indicates that we are going to make linear regression easier to get, as you can also be sure that it expresses how easy it will be to do all of this in Excel!So read on to learn how to do a simple linear regression in Excel, as well as evaluate if you got a good result, and what that means. Let&#39;s go?<h2>Collecting Data</h2>The first step in a regression is data collection. In the case of simple linear regression, we will have only two variables:<ul><li>X Variable: independent variable.</li><li>Y Variable: dependent variable.</li></ul>Firstly, we will work with a simple example. A survey was made considering the average family consumption of several United States families, based on increasing income values, that is, with X dollars, how many Y dollars they consumed. <Image quality="95" width="331" height="231" src="https://images.prismic.io/voitto-blog/ZjdiOGZlN2ItMGIyZi00M2QxLWI1ODEtOWE5NjQ4ZTlmZGRm_eqrrhck6tuaaqkage7zvdzv56i-wimvsp85wm8yngin5_onp7cda3xhstaefugvhw5t9i_cgtampyvrcheay_g91hipi6bpizbkxhl81jgqbrvd34jsz3odqyggjmx-7bvzwyahj" alt="Collecting Data" title="Collecting Data" />Are you tired of missing out on incredible career opportunities because you don&#39;t know about the main Excel tools?So you are in the right place! In the<a target="_blank" rel="nofollow" href="https://thinkleansixsigma.thinkific.com/courses/excel-for-beginners"> Excel for Beginners</a> course, you will learn to work with data in an agile way.In addition, the course is available for FREE, just click on the banner button below and embark on this journey of knowledge!<a target="_blank" rel="nofollow" href="https://thinkleansixsigma.thinkific.com/courses/excel-for-beginners"> <Image quality="95" src="https://images.prismic.io/voitto-blog/45fd4463-eb50-470d-a2e2-1a98155e7fdc_Bot%C3%A3o++Banner+-+Cursos+TLSS+%282%29.png" width="980" height="180" alt="Excel for Beginners" title="Excel for Beginners"/></a><h3>Enabling Data Analysis</h3>After collecting and organizing the data, it is necessary to prepare the data analysis. There are two ways to do this. One by scatter plot and one more complete, by using the Excel data analysis module.We will use Data Analysis, so it is necessary to find out if this option is already enabled in your software. To do this, just click on the Data tab, and see if the desired option appears in the upper right corner, as shown in the image: <Image quality="95" width="1365" height="414" src="https://images.prismic.io/voitto-blog/NTM0NjI3NDctNGJkOS00MTNiLTk2NjItM2NlNGQ2YWZmYTcw_908b9642c.png" alt="Enabling Data Analysis" title="Enabling Data Analysis" /> If this option is not enabled, simply click File &gt; Options &gt; Add-ins &gt; Go ... and then you you must select the 2 options marked on the image: <Image quality="95" width="312" height="419" src="https://images.prismic.io/voitto-blog/ZjhmNTRiZTEtODdkMS00MTc0LTlhNmEtMDg2MDY1NjQ2MTNh_9136ded1b.png" alt="Data Analysis" title="Data Analysis" /> This way, the Data Analysis option will appear and you are ready to go!<h3>Preparing the data analysis</h3>To begin the analysis, simply click on data analysis and the following window will appear: <Image quality="95" width="428" height="222" src="https://images.prismic.io/voitto-blog/MTg0Yjk4OWMtZGQwMy00NzQ2LWEyMzctODAyMmNlZmI4NmVl_911fc3587.png" alt="Preparing the data" title="Preparing the data" /> Just select Regression as shown in the image, and another window will open. <Image quality="95" width="440" height="397" src="https://images.prismic.io/voitto-blog/ZDE5MjMwY2QtNjExZS00MTcxLWEwZTUtYjcxOTJhMjA5ZTQ1_91429a0ba.png" alt="Regression" title="Regression" /> To continue, simply place the cursor as in the image above and select the range of Y and the range of X you want to analyze. First we will analyze the table on the left, that is, you must select for Y and for X the following intervals: <Image quality="95" width="450" height="395" src="https://images.prismic.io/voitto-blog/NjRmOTczNGUtZjkwNC00NDhmLWFhOTEtODYwMjUzZmQ5NTA5_91592dea9.png" alt="Regression" title="Regression" /> Before you click Ok and get your linear regression in excel, you should select the confidence level for 95% because a 5% error is a good estimate in statistics.You can allocate your analysis data either in a new worksheet, as selected in the image above, or in the same worksheet. For our example, we will keep the new worksheet option selected.Also select the option of residuals, residuals plot, standardized residuals, and line fit plots as they will help us in our analyzes. The window will look like the image below: <Image quality="95" width="469" height="391" src="https://images.prismic.io/voitto-blog/NjNjMjhmMWMtYjMzZi00ZjM0LTkyMzYtZWNiNGFlYTliNjhi_9185f45de.png" alt="Regression" title="Regression" /> When you click Ok, you will see the following data in your new worksheet: <Image quality="95" width="1323" height="538" src="https://images.prismic.io/voitto-blog/ZGRkMGQ5YTItZDY2ZS00M2RlLTgyN2MtOWUzZDY0MGJiZjFm_9197a4f21.png" alt="Values on Worksheet" title="Values on Worksheet" /><h3>How do you know if this linear regression makes sense?</h3>It is not enough to just do simple linear regression in excel, we have to evaluate to see if this regression makes sense. After all, some questions come up and need to be answered, such as:<ul><li>Is the equation really adequate for the kind of correlation between the two variables?</li><li>Will the model be useful for predictions?</li></ul>Thus, in order to answer these questions and conclude that the linear regression model is adequate, we must evaluate three points:<ul><li>P-value of the regression: it defines if there is a correlation between the variables</li><li>Residuals analysis: defines whether the equation is actually adequate</li><li>Coefficient of determination (R²): defines the representativity of the variable x to explain the behavior of the variable y.</li></ul>Analyzing these 3 points of simple linear regression we can see if the model is suitable for such study. So let&#39;s begin?<h3>P-value of the regression</h3>The decision rule is as follows:<ul><li>p-value &lt; 0.05 - the correlation between the two variables is significant.</li><li>p-value &gt; 0.05 - correlation between the two variables is not significant</li></ul>For our example we can see that the p-value is 0.005142172, ie, the correlation between two variables is significant: <Image quality="95" width="798" height="84" src="https://images.prismic.io/voitto-blog/YWU5YmYyNmYtYjQzOC00ZWI4LTljZWYtMzUyYTAyMzA1MDE3_92079acf7.png" alt="P Value of the Regression" title="P Value of the Regression" /><h3>Residuals Analysis</h3>The simplest analysis to do in this step is to check whether the predicted Y and the real Y are too far away or not. This difference between the real and the predicted is just the residue, and if it increases as the X values ??increase, it means that something is wrong. <Image quality="95" width="401" height="221" src="https://images.prismic.io/voitto-blog/NzI0YjUzYWQtNDIwZi00MzcwLTk0OWQtNmU4Nzg4ZjUxNjFh_921100a88.png" alt="Residual Analysis" title="Residual Analysis" /> As you can see, the residuals do not increase with the growth of X, so we can move on to the next step.Another graph that we can analyze is the probability plot, which is used to evaluate if the distribution of the residuals is a normal curve or if it is necessary to transform the variables. As our probability graph is close to a straight line, we can conclude that the distribution of the residuals is normal. <Image quality="95" width="395" height="218" src="https://images.prismic.io/voitto-blog/NjNkMWQ4ZDctNGQxOS00OGNhLWJjMDEtMDViMjQxYzMzMDVh_9221cfc1d.png" alt="Normal Probability" title="Normal Probability" /><h3>Coefficient of determination (R²)</h3>In this step we have the following decision rule:<ul><li>The coefficient will be calculated as 0</li><li>The closer to 100%, the greater the representativeness of the variable x when explaining the behavior of the variability that occurs with the variable y, being ideal one above 50%.</li></ul>In our example, we have the following value for the determination coefficient: <Image quality="95" width="226" height="134" src="https://images.prismic.io/voitto-blog/ODI0MmZjNDUtYjM0Zi00YjAxLThjZDYtM2JmMzQyNDViNjU3_92382012e.png" alt="Regression Statistics" title="Regression Statistics" /> Transforming for percentage, we have that the variable X has 96.21% of impact on variable Y, which is an excellent influence.<h3>Did you understand the analysis process?</h3>As important as doing simple linear regression in Excel is whether the result obtained is consistent. After all, if there is no correlation between the variables, it does not make sense to use this statistical tool.In the example that we made all the criteria were met, therefore, it is possible to use simple linear regression to study the process itself. However, linear regression is not always a good model, and it becomes necessary to use more complex tools!<h2>If you made it this far, I have a question for you...</h2>Today one of the most demanded skills in selection processes is the use of Excel. And to give you a good idea of the first steps and first functions, we created the<a target="_blank" rel="nofollow" href="https://conteudo.pocketbook4you.com/ebook-excel-for-beginners"> Excel eBook for Beginners.</a>By downloading our eBook, you will have a step-by-step and practical guide to be able to create your first spreadsheets, and use your most basic and essential functions.Download it now!<a target="_blank" rel="nofollow" href="https://conteudo.pocketbook4you.com/ebook-excel-for-beginners"> <Image quality="95" src="https://images.prismic.io/voitto-blog/9accc706-d6a9-4044-b376-d932d55e820e_Bot%C3%A3o+Banner+Cursos+-+TLSS+%2815%29.png" width="1025" height="205" alt="[EBOOK] Excel for Beginners" title="[EBOOK] Excel for Beginners"/></a>