Despite not being as powerful as software specifically for statistics, Excel is really adept at running basic calculations, even without add-ins (although there are some add-ons that make it even better Power Up Excel with 10 add-ons to the process, Analyze and visualize data like a Pro Power Up Excel with 10 add-ins to process, analyze and visualize data like a Pro Vanilla Excel is awesome but you can make it even more powerful with add-ins Any information you need to process someone has probably created a Excel for her. Here's a selection. Read More).
You probably know you can do arithmetic, but did you know you can also quickly get percentage changes, means, sample and population standard deviation, standard error, and Student's t-tests??
Excel has a lot of statistical power if you know how to use it. Next we will look at some of the more basic statistical calculations. Let's get started!
Calculating percentages in Excel is as simple as anywhere else:just divide two numbers and multiply by 100. Let's say we're calculating the percentage of 347 out of 521.
Simply divide 347 by 521 using the syntax =347/521 . (If you're not familiar with Excel, the equals sign tells Excel that you want it to calculate something. Just enter the equation after that and press Enter to run the calculation.)
It now has a decimal value (in this case, .67). To convert it to a percentage, press Ctrl + Shift + 5 on your keyboard (this is a very useful Excel keyboard shortcut 60 Essential Microsoft Office Keyboard Shortcuts for Word, Excel and PowerPoint 60 Essential Microsoft Office Keyboard Shortcuts for Word, Excel and PowerPoint Hundreds of keyboard shortcuts are available at Office, but these are the most important ones to know because they will save you a lot of time Read More
You can also change the cell format by right-clicking the cell, selecting Format cells , choosing Percentage , and clicking OK .
Note that changing the cell format takes care of the "multiply by 100" step. If you multiply by 100 and then change the format to percent, you'll get another multiplication (and the wrong number).
Tipping: Learn How to Create Dropdown Lists for Excel Cells How to Create a Dropdown List in Excel How to Create a Dropdown List in Excel Excel supports versatile dropdown list options. Use them to control user input. We'll show you how to add dropdown lists to your Excel spreadsheets. Read more.
Calculating the percentage increase is similar. Let's say our first measurement is 129 and our second is 246. What is the percent increase?
To start, you'll need to find the gross increase, so subtract the starting value from the second value. In our case, we will use . =246-129 get a result of 117.
Now take the resulting value (the raw change) and divide it by the original measurement. In our case, that is. =117/129 . That gives us a decimal change of .906. You can also get all this information in a single formula like this:
Use the same process as above to convert this to a percentage, and you'll see we have a 91 percent change. Do a quick check:117 is almost equal to 129, so this makes sense. If we had calculated an exchange value of 129, the percentage change would have been 100 percent.
One of the most useful built-in functions of Excel. 16 Excel Formulas to Help You Solve Real-Life Problems. 16 Excel Formulas to Help You Solve Real-Life Problems. The right tool is half the job. Excel can solve calculations and process data faster than your calculator can. We show you the key Excel formulas and show you how to use them. Read More calculates the mean (average) of a set of numbers. If you haven't used an Excel function before, you'll be impressed by how easy it is. Just type the name of the function, select the cells you want to apply it to, and press Enter.
In our example here, we have a series of measurements that we need the average of. We will click on a new cell and type =AVERAGE ( , then use the mouse to select the relevant cells (you can also type the cell range if you want). Close the parentheses with a ) and you will have a formula that looks like this:=AVERAGE(B4:B16)
Hit Enter , and you will get the average! That's all there is to it.
From a student t -The test calculates the chances that two samples come from the same population. A Statistics Lesson goes beyond this article, but you can read more about the different types of students. t -try these free resources to learn statistics Learn statistics for free with these 6 resources Learn statistics for free with these 6 resources Statistics have a reputation for being a difficult subject to understand. But learning from the right resource will help you understand your poll results, election reports, and your statistics class assignments in no time. Read More (Statistics Hell is my personal favorite).
In summary, however, the P value derived from a t -the test will tell you if there is a significant difference between two sets of numbers.
Let's say you have two measurements from the same group and you want to see if they are different. Let's say you weighed a group of participants, put them through personal training, and then weighed them again. This is called a match t -test, and let's start with this.
Excel's T.TEST function is what you need here. The syntax looks like this:
= T.TEST (array1, array2, colas, tipo)array1 and array2 are the groups of numbers you want to compare. The queues argument should be set to "1" for a one queue test and "2" for a two queue test.
The type argument can be set to “1,” “2,” or “3.” We'll set it to “1” for this example because that's how we tell Excel that we're doing a t pair. -test.
This is what the formula for our example will look like:
Now we just hit Enter to get our result! It is important to remember that this result is the p-value . In most fields, a p-value less than .05 indicates a significant result.
The basics of the test are the same for all three types. As mentioned, a "1" in the type field creates a t match. -Test. A “2” runs a two-sample test with equal variance, and a “3” runs a two-sample test with unequal variance. (Using the latter, Excel runs a Welch t -test.)
Calculating the standard deviation in Excel is as easy as calculating the average. This time, it will use the STDEV.S or STDEV.P functions, though.
STDEV.S should be used when your data is a sample from a population. STDEV.P, on the other hand, works when it calculates the standard deviation for an entire population. Both functions ignore text and logical values (if you want to include them, you'll need STDEVA or STDEVPA).
To determine the standard deviation of a set, simply type =STDEV.S() or =STDEV.P() and insert the range of numbers in the parentheses. You can click and drag or type the range.
At the end, you will have a number:that is your standard deviation.
The standard error is closely related to the standard deviation. And even though Excel doesn't have a function to calculate it, you can quickly find it with minimal effort.
To find the standard error, divide the standard deviation by the square root of n , the number of values in your data set. You can get this information with a single formula:
= STDEV.S (array1) / SQRT (COUNT (array1))If you're using text or logical values in your array, you'll need to use COUNTA instead.
This is how we calculate the standard error with our data set:
Can you use Excel for statistics and complex calculations? Yes. Will it work as well as dedicated statistical software like SPSS or SAS? No. But you can still calculate percentages, averages, standard deviations, and even t -tests.
When you need a quick calculation and your data is in Excel, you don't need to import it into a different software. And that will save you time. You can also use Excel's goal-seeking feature to solve equations even faster. How to use Excel's Goal Seek and Solver to solve for unknown variables. Cell with goal seeker or a more complicated equation with Solver. We will show you how it works. Read more.
Don't forget to put your data into aesthetic and informative charts. How to Create Powerful Charts and Charts in Microsoft Excel How to Create Powerful Charts and Charts in Microsoft Excel A good chart can make the difference between getting your point across or putting everyone to sleep off. We show you how to create powerful charts in Microsoft Excel that engage and inform your audience. Read More And it would also help to master IF statements in Excel. How to use IF statements in Microsoft Excel. How to use IF statements in Microsoft Excel. Declarations in Excel. Read more.