This resource covers the 12 most useful Excel functions for data analysis. And the good news is that most Excel users have a toolkit of just a few functions that complete most of their needs. The most useful Excel functions are those that make the task seem easy.
![]() The Data Analysis Toolpak in ExcelIf you’re analyzing data in Excel, then it’s natural to make use of the tools that Microsoft provides for you. We’ll begin with one-way ANOVA, which looks at the effect of a single factor. Large p-values might lead you to act like the null hypothesis is true, even though you know that it’s not really true, just a reasonable model.To learn more check out this glossary of Lean Six Sigma termsLet’s work through a practical example in Excel. Small p-values make you think that the null hypothesis is not a reasonable model. Excel Descriptive Statistics How To Turn ItNext to Manage, select Excel Add-ins and click Go In the Excel Options Window, choose Add-ins The Toolpak is an Excel add-in from Microsoft that’s included with Excel, but isn’t turned on.Here’s how to turn it on in the Microsoft Windows operating system. Chrome helper cleaner macData arrangement for one-way ANOVA in ExcelIf you’ve been using Excel for a long time, you’ve gotten used to the idea that the spreadsheet is cell-based. You’ve decided that you’re going to measure the strengths of tape samples from different suppliers yourself so that you can see whether there’s any practical difference in the strengths of the bonds using your machine and your boxes. You’ve invested in an automatic taping machine that applies heat to tape to create strong bonds. Essentially, you can use it anytime you have only one set of groups to compare.Let’s keep going with our tape example. Select the data and click the down arrowResults for one-way ANOVA in Excel: Summary statisticsFirst, let’s take a minute to look at the summary statistics of each group.In particular, the averages, in ascending order, are about 9.67, 9.77, and 9.84. Next to Input Range click the up arrow5. Select Anova: Single Factor and click OK4. The data arrangement will matter when you want to use some of the other offerings in the Data Analysis Toolpak or a software package for data analysis, like Minitab Statistical Software.If you’d like to follow along with data that’s already prearranged, you can use the following Excel file:With the Data Analysis Toolpak installed and your data in columns, you can perform the following steps in Excel to get the results of the one-way ANOVA analysis. Results for one-way ANOVA in Excel: Hypothesis testsRemember that small p-values give us low confidence in the null hypothesis.The value of about 0.27 is higher than the level where people traditionally agree that there is strong evidence against the null hypothesis. If you think that the means are similar, then you’ll expect to see a larger p-value for the hypothesis test. If kilograms aren’t very familiar to you, you can think of the tape with the lowest average being strong enough to hold about 60 apples and the tape with the highest average being strong enough to hold about 62 apples.That should be enough for us to start to think about what we expect about the null hypothesis for the ANOVA. The difference between the largest mean and the smallest mean is about 0.17 kg. Instead of doing the test only on the factor of tape supplier, you want to make sure that you have the right tape for the right box.One approach could be to do a one-way ANOVA where you use more than one factor to define the groups. What if you have more factors?Let’s suppose that you’re considering not only the tape supplier, but also choosing among some different boxes.You know that the roughness and absorbency of the box might affect how strong the tape holds to it. For example, you might consider price or your confidence that the supplier can fill your orders on time. By extension, there’s a lot of uncertainty about whether any one average is larger than another.If those 2 apples worth of strength are so much that you would make a different decision about the tape suppliers because of that difference, then you’ll need more data.On the other hand, if those 2 apples don’t sound like a big deal, this is a good place to decide that you can choose the supplier with other criteria. This data arrangement, called a two-way table, would look like this:If you’d like to follow along with data that’s already prearranged, you can use the followingWith the Data Analysis Toolpak installed and your data in columns, you can perform the following steps in Excel to get the results of the two-way ANOVA analysis. Data for one factor need to be in different columns.Data for the second factor need to be in consecutive rows.For Excel to work, you’ll need to have the same number of measurements for all of your groups.You don’t necessarily have to provide the factor label for the rows, but it’s good practice, especially if you might want to graph your data in Excel later. Data Arrangement for Two-Way ANOVA in ExcelExcel can be flexible with your data arrangement for one-way ANOVA, but is strict about the data arrangement when you do a two-way ANOVA with replication through the Data Analysis Toolpak. If the one-way ANOVA said that there was a difference between those two groups, then you still wouldn’t know how much of the difference was from the change in tape, the change in box, or a change that depended on both simultaneously.An analysis to get this type of information when you have two factors is two-way ANOVA. Another group might be the second tape supplier on a second box type.The disadvantage of this approach is that it doesn’t let you distinguish the effect of different factors.
0 Comments
Leave a Reply. |
AuthorMinerva ArchivesCategories |