ANOVA Test

Done in Excel

Compared To By Hand

ANOVA is something you would do by hand, ONLY if you absolutely had to. I remember being forced to perform these calculations by hand in statistics class when I was getting my MBA. I also remember wondering why in the world we were doing that when we all knew Excel. Still don’t have an answer to that question. It seemed a little like doing multiplication on a slide rule even though a calculator was readily available. My father once showed me his old slide rule and how facile he had become with it. It was almost magic. I never got the urge to figure it out though. Sorry Dad.

The video below shows a statistical procedure called Single-Factor ANOVA (the simplest type of ANOVA) being solved in Excel and then solved by hand. I’m hoping that the torturous hand calculations in the video only serve to more strongly contrast the ease of doing statistics in Excel.

Step-By-Step Video Showing How To Do Single-Factor ANOVA In Excel and Also How To Do It By Hand:

(Is Your Sound Turned On?)

Why Excel Is A Good Starting Point To Teach Statistics

The point of this article and the linked video is to persuade statistics teachers to focus their efforts on using tools like Excel right from the start. Slogging through those hand calculations is almost never a good thing. Probably the fastest way to make a statistics student to seriously hate statistics is to force him or her to do calculation-intensive tasks like regression and ANOVA by hand.

As an Internet marketing manager who does a lot of statistics on the job, I am SOOO glad I learned how to do all of my statistic procedures on Excel. Believe it or not, statistics is actually kind of fun when you have such a convenient tool like Excel (I’ve been called a Propeller Head more than once). There are plenty of other fine statistical tools like Minitab, SPSS, and SAS. But….I (like almost any other business manager) have a pretty good grasp of Excel. Honestly, I don’t really have no desire to learn SAS (or any other statistical software that costs thousands of dollars) when I’ve got Excel. If I can use Excel instead, you bet I’m gonna.

Here’s a little info about the ANOVA test that was run in the above video:

What Is ANOVA?

ANOVA stand for Analysis of Variance. It is a test to determine if three or more variations of each of one or more factors have an effect on a population. ANOVA tests the Null Hypothesis of each factor. The Null Hypothesis of each factor states that varying the factor has no effect on a population. The ANOVA test results in either acceptance or rejection of the Null Hypothesis within a specified degree of certainty.

The single-factor ANOVA test in the linked video evaluates whether different closing methods affect the probability that a sale will close. The Null Hypothesis states that varying the closing method does not affect the number of sales that get closed. All other factors, including the abilities of the individual salespeople, are assumed to be the same.

The Null Hypothesis

Acceptance or rejection of the Null Hypothesis can be determined by either the P Value or the F Statistic obtained by the calculations. Both the P Value and the F Statistic are equivalent to each other and are nearly interchangeable. The video provide a detailed explanation of the following: We accept the Null Hypothesis if the P Value is greater than Alpha (Alpha = 1 – Required Degree of Certainty) or, equivalently, if the calculated F Statistics is less than F Critical. We reject the Null Hypothesis if the opposite is true. Rejection of the Null Hypothesis implies that variation of the associated factor did affect the outcome.

Doing ANOVA By Hand vs. By Excel

Doing ANOVA in Excel takes just a few seconds with little possibility of error if the data is inserted correctly. Doing ANOVA by hand takes a LONG time and has LOTS of opportunities for error. Here, the above video of step-by-step ANOVA video will, hopefully, will convince you of that.

Here Is the Original Problem to Be Solved With Single-Factor ANOVA:

anova, analysis of variance, anova testing, one way anova, anova test, 2 way anova, anova spss, two factor anova, anova two way, anova analysis, anova assumption, statistical analysis in excel

Click On Image To See Enlarged View

A group of 4 salespeople used a different closing method exclusively each week for 3 weeks. The sales totals for each salesperson using each method are shown above. We need to determine within 95% certainty whether varying the closing method affected sales numbers or not. No other factors were varied during the 3-week duration of this test.

Here is the Problem Solved in Excel in One Step:

Click On Image To See Enlarged View

The Excel output shows the P Value associated with the closing methods to be 0.0144. This is significantly less than the alpha of 0.05, so we can reject the Null Hypothesis and state with 95% that varying the closing method did affect sales totals. Remember, it took less than 10 seconds to input the data from the excel spread sheet and get the above output. Compare this to doing the same problem by hand below and arriving at the same answer.

Now, Here is The Same Problem Done By Hand

Click On Image To See Enlarged View

Yup, same answer as Excel, but now I've got a headache!

Hopefully This Article Touched a Nerve...

Hopefully this article touched a nerve with the poor folks out there who were forced to do ANOVA by hand in statistics class. There might even be a few statistics teachers who would rather have taught ANOVA in Excel than having had to do it by hand in front of the class room. I've had to teach ANOVA by hand to a class or two and it wasn't the funnest thing I've ever done.

If you agree or disagree with this article, let the world know with your comments below. Your input is highly valued!

If You Like This, Then Share It...

Excel Master Series Blog Directory

Statistical Topics and Articles In Each Topic

Become an Excel Statistical Master

Excel Master Series - MBA-level statistics - Over 1,100+ Pages of Easy-To-Follow Instructions in Excel

It's a Full
Easy-To-Follow
MBA Course in Business Statistics

ALL IN EXCEL

&

MUCH Clearer

Than Your Text

Book

Download the
1,100+ Page Excel Statistical Master now

More Easy-To-

Follow eManuals

That You Will

Master Quickly

Step-By-Step Optimization With Excel Solver

What's In It?

For anyone who wants to be operating at a high level with the Excel Solver quickly, this is the book for you. Step-By-Step Optimization With Excel Solver is a 200+ page .pdf e-manual of simple yet thorough explanations on how to use the Excel Solver to solve today’s most widely known optimization problems. Loaded with screen shots that are coupled with easy-to-follow instructions, this book will simplify many difficult optimization problems and make you a master of the Excel Solver almost immediately.

Here are just some of the Solver optimization problems that are solved completely with simple-to-understand instructions and screen shots in this e-manual:

• The famous “Traveling Salesman” problem using Solver’s Alldifferent constraint and the Solver’s Evolutionary method to find the shortest path to reach all customers. This also provides an advanced use of the Excel INDEX function.

• The well-known “Knapsack Problem” which shows how optimize the use of limited space while satisfying numerous other criteria.

• How to perform nonlinear regression and curve-fitting on the Solver using the Solver’s GRG Nonlinear solving method.

• How to solve the “Cutting Stock Problem” faced by many manufacturing companies who are trying to determine the optimal way to cut sheets of material to minimize waste while satisfying customer orders.

• Portfolio optimization to maximize return or minimize risk.

• Venture capital investment selection using the Solver’s Binary constraint to maximize Net Present Value of selected cash flows at year 0. Clever use of the If-Then-Else statements makes this a simple problem.

• How use Solver to minimize the total cost of purchasing and shipping goods from multiple suppliers to multiple locations.

• How to optimize the selection of different production machine to minimize cost while fulfilling an order.

• How to optimally allocate a marketing budget to generate the greatest reach and frequency or number of inbound leads at the lowest cost.

Step-By-Step Optimization With Excel Solver has complete instructions and numerous tips on every aspect of operating the Excel Solver. You’ll fully understand the reports and know exactly how to tweek all of the Solver’s settings for total custom use. This e-manual also provides lots of inside advice and guidance on setting up the model in Excel so that it will be as simple and intuitive as possible to work with.

All of the optimization problems in this book are solved step-by-step using a 6-step process that works every time. In addition to detailed screen shots and easy-to-follow explanations on how to solve every optimization problem in the book, a link is provided to download an Excel workbook that has all problems completed exactly as they are in this e-manual.

Step-By-Step Optimization With Excel Solver is exactly the e-manual you need if you want to be optimizing at an advanced level with the Excel Solver quickly.

*******************

Become an Excel Statistical Master

It's a Full
Easy-To-Follow
MBA Course in Business Statistics

ALL IN EXCEL

&

MUCH Clearer

Than Your Text

Book

Download the
1,100+ Page Excel Statistical Master now

More Easy-To-

Follow eManuals

That You Will

Master Quickly

*******************

Become an Excel Statistical Master

It's a Full
Easy-To-Follow
MBA Course in Business Statistics

ALL IN EXCEL

&

MUCH Clearer

Than Your Text

Book

Download the
1,100+ Page Excel Statistical Master now

More Easy-To-

Follow eManuals

That You Will

Master Quickly

*******************

Become an Excel Statistical Master

It's a Full
Easy-To-Follow
MBA Course in Business Statistics

ALL IN EXCEL

&

MUCH Clearer

Than Your Text

Book

Download the
1,100+ Page Excel Statistical Master now

Immediate, Absolute, No-Questions-Asked, Money-Back Guarantee If Not TOTALLY, 100% Satisfied. In Other Words, If Any Excel Master Series eManual That You've Purchased Here Does Not Provide Instructions That Are CRYSTAL CLEAR and EASY TO UNDERSTAND, You Get All Of Your Money Back Immediately and Keep the eManual. Guaranteed!

Meet The Author