{"id":2070,"date":"2021-05-06T20:34:25","date_gmt":"2021-05-06T20:34:25","guid":{"rendered":"https:\/\/courses.lumenlearning.com\/frontrange-introstats1\/?post_type=chapter&#038;p=2070"},"modified":"2021-08-13T20:59:49","modified_gmt":"2021-08-13T20:59:49","slug":"notes-for-excel-placeholder","status":"publish","type":"chapter","link":"https:\/\/courses.lumenlearning.com\/frontrange-introstats1\/chapter\/notes-for-excel-placeholder\/","title":{"raw":"Appendix G:  Notes for Excel","rendered":"Appendix G:  Notes for Excel"},"content":{"raw":"<h2><strong>Load the Analysis ToolPak in Excel <\/strong><\/h2>\r\nIf you need to develop complex statistical or engineering analyses, you can save steps and time by using the Analysis ToolPak. You provide the data and parameters for each analysis, and the tool uses the appropriate statistical or engineering macro functions to calculate and display the results in an output table. Some tools generate charts in addition to output tables.\r\n\r\nThe data analysis functions can be used on only one worksheet at a time. When you perform data analysis on grouped worksheets, results will appear on the first worksheet and empty formatted tables will appear on the remaining worksheets. To perform data analysis on the remainder of the worksheets, recalculate the analysis tool for each worksheet.\r\n<ol>\r\n \t<li>Click the <strong>File<\/strong> tab, click <strong>Options<\/strong>, and then click the <strong>Add-Ins<\/strong> category.<\/li>\r\n<\/ol>\r\n<p style=\"padding-left: 30px;\">If you're using Excel 2007, click the <strong>Microsoft Office Button<\/strong>, and then click <strong>Excel Options<\/strong><\/p>\r\n\r\n<ol start=\"2\">\r\n \t<li>In the <strong>Manage<\/strong> box, select <strong>Excel Add-ins<\/strong> and then click <strong>Go<\/strong>.<\/li>\r\n<\/ol>\r\n<p style=\"padding-left: 30px;\">If you're using Excel for Mac, in the file menu go to <strong>Tools<\/strong> &gt; <strong>Excel Add-ins.<\/strong><\/p>\r\n\r\n<ol start=\"3\">\r\n \t<li>In the <strong>Add-Ins<\/strong> box, check the <strong>Analysis ToolPak<\/strong> check box, and then click <strong>OK<\/strong>.\r\n<ul>\r\n \t<li>o If <strong>Analysis ToolPak<\/strong> is not listed in the <strong>Add-Ins available<\/strong> box, click <strong>Browse<\/strong> to locate it.<\/li>\r\n \t<li>o If you are prompted that the Analysis ToolPak is not currently installed on your computer, click <strong>Yes<\/strong> to install it.<\/li>\r\n<\/ul>\r\n<\/li>\r\n<\/ol>\r\n<a href=\"https:\/\/support.microsoft.com\/en-us\/office\/load-the-analysis-toolpak-in-excel-6a63e598-cd6d-42e3-9317-6b40ba1a66b4?ns=excel&amp;version=19&amp;ui=en-us&amp;rs=en-us&amp;ad=us\">Load the Analysis ToolPak in Excel - Office Support (microsoft.com)<\/a>\r\n<h2>Excel Functions for MAT 135<\/h2>\r\nrandbetween (bottom, top) Gives a random number between the two numbers entered for \u201cbottom\u201d and \u201ctop\u201d.\u00a0 Copy this function down to create a list of random numbers.\r\n\r\ncorrel (array1, array2) \u00a0 Calculates the correlation coefficient between two data sets.\r\n\r\nData Analysis Toolpak:\u00a0 correlation \u00a0 Output will give you a matrix which gives the correlation of x vs. x, x vs. y and y vs y.\u00a0 Only use x vs. y\r\n\r\nData Analysis Toolpak:\u00a0 regression \u00a0 Can be used to find the regression line (but using a trendline on the scatter plot is easier).\u00a0 The advantage of this function is the residual plot.\r\n\r\naverage ( \u00a0 \u00a0 ) \u00a0 Gives the arithmetic mean for a set of given numbers.\r\n\r\nmedian ( \u00a0 \u00a0 ) \u00a0 Gives the median for a set of given numbers.\r\n\r\nmode.sgnl ( \u00a0 ) \u00a0 Gives the mode for a set of given numbers.\u00a0 \u00a0 This function will return only one mode, even if multiple values are repeated the same number of times. \u00a0 The mode.mult(\u00a0 ) does return multiple modes but you have to have several cells highlighted and use the ctrl, shift and enter keys simultaneously.\u00a0 It may almost be easier to sort your data and look for the mode or modes yourself.\r\n\r\nmin( \u00a0 )\u00a0 Gives the lowest value in the data set*.\r\n\r\nmax( \u00a0 )\u00a0 Gives the highest value in the data set*.\r\n\r\n*The min and max functions can be combined to find the range of a data set in one step without sorting [=max( \u00a0 )-min( \u00a0 )].\r\n\r\nvar.p(\u00a0 )\u00a0 Gives the population variance.\r\n\r\nvar.s(\u00a0 )\u00a0 Gives the sample variance.\r\n\r\nstdev.p (\u00a0 ) \u00a0 Gives the population standard deviation.\r\n\r\nstdev.s ( ) \u00a0 Gives the sample standard deviation.\r\n\r\nData Analysis Toolpak:\u00a0 Descriptive Statistics\u00a0 \u00a0 Returns many descriptive statistics such as mean, sample standard deviation and sample variance.\r\n\r\nNote \u2013 do not use the Excel quartile functions.\u00a0 To find Q1 and Q3, either sort and count or after finding the overall median (Q2), take the median of the lower half to find Q1 and the median of the upper half to find (Q3).\r\n\r\nfact(n) \u00a0 Use this function to calculate n!.\r\n\r\npermut (number, number chosen) \u00a0 Gives the number of permutations.\u00a0 Number is the number of objects and number_chosen is the number taken at a time.\r\n\r\ncombin (number, number chosen) \u00a0 Gives the number of combinations.\u00a0 Number is the number of objects and number_chosen is the number taken at a time.\r\n\r\nbinom.dist (number_s, trials, probability_s, cumulative) \u00a0 Use true or 1 for cumulative if you want the sum of the probabilities up to and including that value and false or 0 if you want the probability of that value only.\r\n\r\nnorm.s.dist (z, true or 1) \u00a0 Gives area under the curve to the left of z using the standard normal distribution (mean of 0 and std dev of 1).\u00a0 Use 1 \u2013 norm.s.dist for area to the right. Only use cumulative true or 1 \u2013 never use cumulative false or 0.\r\n\r\nnorm.dist (X, mean, std dev, true or 1) \u00a0 Gives the normal cumulative distribution function (area under the curve to the left of X).\u00a0 Use 1 \u2013 norm.dist for area to the right). Only use cumulative true or 1 \u2013 never use cumulative false or 0.\r\n\r\nnorm.s.inv (area) \u00a0 Gives the inverse of the standard normal distribution (gives z when probability\/area to the left is given).\r\n\r\nnorm.inv (area, mean, std dev) \u00a0 Gives the inverse of the normal cumulative distribution (gives X when probability\/area to the left is given).\r\n\r\nconfidence.t (alpha, standard dev, size) \u00a0 Returns E for a population mean using the t distribution.\u00a0 To find confidence interval, add and subtract E from the sample mean to get the upper bound and lower bound.\r\n\r\nt.inv(probability, deg_freedom)\u00a0 Returns the t critical value with the specified probability to the left.\r\n\r\nt.dist(X, deg_freedom, cumulative)\u00a0 Gives the t cumulative distribution function (area under the t distribution curve for the given degrees of freedom to the left of X).\u00a0 Use 1 \u2013 t.dist for area to the right.\u00a0 Can be used to find P-values. Only use cumulative true or 1 \u2013 never use cumulative false or 0.\r\n\r\n&nbsp;","rendered":"<h2><strong>Load the Analysis ToolPak in Excel <\/strong><\/h2>\n<p>If you need to develop complex statistical or engineering analyses, you can save steps and time by using the Analysis ToolPak. You provide the data and parameters for each analysis, and the tool uses the appropriate statistical or engineering macro functions to calculate and display the results in an output table. Some tools generate charts in addition to output tables.<\/p>\n<p>The data analysis functions can be used on only one worksheet at a time. When you perform data analysis on grouped worksheets, results will appear on the first worksheet and empty formatted tables will appear on the remaining worksheets. To perform data analysis on the remainder of the worksheets, recalculate the analysis tool for each worksheet.<\/p>\n<ol>\n<li>Click the <strong>File<\/strong> tab, click <strong>Options<\/strong>, and then click the <strong>Add-Ins<\/strong> category.<\/li>\n<\/ol>\n<p style=\"padding-left: 30px;\">If you&#8217;re using Excel 2007, click the <strong>Microsoft Office Button<\/strong>, and then click <strong>Excel Options<\/strong><\/p>\n<ol start=\"2\">\n<li>In the <strong>Manage<\/strong> box, select <strong>Excel Add-ins<\/strong> and then click <strong>Go<\/strong>.<\/li>\n<\/ol>\n<p style=\"padding-left: 30px;\">If you&#8217;re using Excel for Mac, in the file menu go to <strong>Tools<\/strong> &gt; <strong>Excel Add-ins.<\/strong><\/p>\n<ol start=\"3\">\n<li>In the <strong>Add-Ins<\/strong> box, check the <strong>Analysis ToolPak<\/strong> check box, and then click <strong>OK<\/strong>.\n<ul>\n<li>o If <strong>Analysis ToolPak<\/strong> is not listed in the <strong>Add-Ins available<\/strong> box, click <strong>Browse<\/strong> to locate it.<\/li>\n<li>o If you are prompted that the Analysis ToolPak is not currently installed on your computer, click <strong>Yes<\/strong> to install it.<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n<p><a href=\"https:\/\/support.microsoft.com\/en-us\/office\/load-the-analysis-toolpak-in-excel-6a63e598-cd6d-42e3-9317-6b40ba1a66b4?ns=excel&amp;version=19&amp;ui=en-us&amp;rs=en-us&amp;ad=us\">Load the Analysis ToolPak in Excel &#8211; Office Support (microsoft.com)<\/a><\/p>\n<h2>Excel Functions for MAT 135<\/h2>\n<p>randbetween (bottom, top) Gives a random number between the two numbers entered for \u201cbottom\u201d and \u201ctop\u201d.\u00a0 Copy this function down to create a list of random numbers.<\/p>\n<p>correl (array1, array2) \u00a0 Calculates the correlation coefficient between two data sets.<\/p>\n<p>Data Analysis Toolpak:\u00a0 correlation \u00a0 Output will give you a matrix which gives the correlation of x vs. x, x vs. y and y vs y.\u00a0 Only use x vs. y<\/p>\n<p>Data Analysis Toolpak:\u00a0 regression \u00a0 Can be used to find the regression line (but using a trendline on the scatter plot is easier).\u00a0 The advantage of this function is the residual plot.<\/p>\n<p>average ( \u00a0 \u00a0 ) \u00a0 Gives the arithmetic mean for a set of given numbers.<\/p>\n<p>median ( \u00a0 \u00a0 ) \u00a0 Gives the median for a set of given numbers.<\/p>\n<p>mode.sgnl ( \u00a0 ) \u00a0 Gives the mode for a set of given numbers.\u00a0 \u00a0 This function will return only one mode, even if multiple values are repeated the same number of times. \u00a0 The mode.mult(\u00a0 ) does return multiple modes but you have to have several cells highlighted and use the ctrl, shift and enter keys simultaneously.\u00a0 It may almost be easier to sort your data and look for the mode or modes yourself.<\/p>\n<p>min( \u00a0 )\u00a0 Gives the lowest value in the data set*.<\/p>\n<p>max( \u00a0 )\u00a0 Gives the highest value in the data set*.<\/p>\n<p>*The min and max functions can be combined to find the range of a data set in one step without sorting [=max( \u00a0 )-min( \u00a0 )].<\/p>\n<p>var.p(\u00a0 )\u00a0 Gives the population variance.<\/p>\n<p>var.s(\u00a0 )\u00a0 Gives the sample variance.<\/p>\n<p>stdev.p (\u00a0 ) \u00a0 Gives the population standard deviation.<\/p>\n<p>stdev.s ( ) \u00a0 Gives the sample standard deviation.<\/p>\n<p>Data Analysis Toolpak:\u00a0 Descriptive Statistics\u00a0 \u00a0 Returns many descriptive statistics such as mean, sample standard deviation and sample variance.<\/p>\n<p>Note \u2013 do not use the Excel quartile functions.\u00a0 To find Q1 and Q3, either sort and count or after finding the overall median (Q2), take the median of the lower half to find Q1 and the median of the upper half to find (Q3).<\/p>\n<p>fact(n) \u00a0 Use this function to calculate n!.<\/p>\n<p>permut (number, number chosen) \u00a0 Gives the number of permutations.\u00a0 Number is the number of objects and number_chosen is the number taken at a time.<\/p>\n<p>combin (number, number chosen) \u00a0 Gives the number of combinations.\u00a0 Number is the number of objects and number_chosen is the number taken at a time.<\/p>\n<p>binom.dist (number_s, trials, probability_s, cumulative) \u00a0 Use true or 1 for cumulative if you want the sum of the probabilities up to and including that value and false or 0 if you want the probability of that value only.<\/p>\n<p>norm.s.dist (z, true or 1) \u00a0 Gives area under the curve to the left of z using the standard normal distribution (mean of 0 and std dev of 1).\u00a0 Use 1 \u2013 norm.s.dist for area to the right. Only use cumulative true or 1 \u2013 never use cumulative false or 0.<\/p>\n<p>norm.dist (X, mean, std dev, true or 1) \u00a0 Gives the normal cumulative distribution function (area under the curve to the left of X).\u00a0 Use 1 \u2013 norm.dist for area to the right). Only use cumulative true or 1 \u2013 never use cumulative false or 0.<\/p>\n<p>norm.s.inv (area) \u00a0 Gives the inverse of the standard normal distribution (gives z when probability\/area to the left is given).<\/p>\n<p>norm.inv (area, mean, std dev) \u00a0 Gives the inverse of the normal cumulative distribution (gives X when probability\/area to the left is given).<\/p>\n<p>confidence.t (alpha, standard dev, size) \u00a0 Returns E for a population mean using the t distribution.\u00a0 To find confidence interval, add and subtract E from the sample mean to get the upper bound and lower bound.<\/p>\n<p>t.inv(probability, deg_freedom)\u00a0 Returns the t critical value with the specified probability to the left.<\/p>\n<p>t.dist(X, deg_freedom, cumulative)\u00a0 Gives the t cumulative distribution function (area under the t distribution curve for the given degrees of freedom to the left of X).\u00a0 Use 1 \u2013 t.dist for area to the right.\u00a0 Can be used to find P-values. Only use cumulative true or 1 \u2013 never use cumulative false or 0.<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"author":167848,"menu_order":8,"template":"","meta":{"_candela_citation":"[]","CANDELA_OUTCOMES_GUID":"","pb_show_title":"on","pb_short_title":"","pb_subtitle":"","pb_authors":[],"pb_section_license":""},"chapter-type":[],"contributor":[],"license":[],"class_list":["post-2070","chapter","type-chapter","status-publish","hentry"],"part":1635,"_links":{"self":[{"href":"https:\/\/courses.lumenlearning.com\/frontrange-introstats1\/wp-json\/pressbooks\/v2\/chapters\/2070","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/courses.lumenlearning.com\/frontrange-introstats1\/wp-json\/pressbooks\/v2\/chapters"}],"about":[{"href":"https:\/\/courses.lumenlearning.com\/frontrange-introstats1\/wp-json\/wp\/v2\/types\/chapter"}],"author":[{"embeddable":true,"href":"https:\/\/courses.lumenlearning.com\/frontrange-introstats1\/wp-json\/wp\/v2\/users\/167848"}],"version-history":[{"count":5,"href":"https:\/\/courses.lumenlearning.com\/frontrange-introstats1\/wp-json\/pressbooks\/v2\/chapters\/2070\/revisions"}],"predecessor-version":[{"id":2764,"href":"https:\/\/courses.lumenlearning.com\/frontrange-introstats1\/wp-json\/pressbooks\/v2\/chapters\/2070\/revisions\/2764"}],"part":[{"href":"https:\/\/courses.lumenlearning.com\/frontrange-introstats1\/wp-json\/pressbooks\/v2\/parts\/1635"}],"metadata":[{"href":"https:\/\/courses.lumenlearning.com\/frontrange-introstats1\/wp-json\/pressbooks\/v2\/chapters\/2070\/metadata\/"}],"wp:attachment":[{"href":"https:\/\/courses.lumenlearning.com\/frontrange-introstats1\/wp-json\/wp\/v2\/media?parent=2070"}],"wp:term":[{"taxonomy":"chapter-type","embeddable":true,"href":"https:\/\/courses.lumenlearning.com\/frontrange-introstats1\/wp-json\/pressbooks\/v2\/chapter-type?post=2070"},{"taxonomy":"contributor","embeddable":true,"href":"https:\/\/courses.lumenlearning.com\/frontrange-introstats1\/wp-json\/wp\/v2\/contributor?post=2070"},{"taxonomy":"license","embeddable":true,"href":"https:\/\/courses.lumenlearning.com\/frontrange-introstats1\/wp-json\/wp\/v2\/license?post=2070"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}