Excel Statistical functions in Excel Course contents Overview

  • Slides: 37
Download presentation
Excel Statistical functions in Excel

Excel Statistical functions in Excel

Course contents • Overview: Using statistical functions, formulas • Lesson 1: The basics of

Course contents • Overview: Using statistical functions, formulas • Lesson 1: The basics of Excel and • Lesson 2: Write good formulas • Lesson 3: Which function to use? Statistical functions in Excel

Overview: Using statistical functions, formulas Learn about using statistical functions and formulas in Excel,

Overview: Using statistical functions, formulas Learn about using statistical functions and formulas in Excel, from the very basics to choosing the right function for you. Learn also how to troubleshoot common problems. Statistical functions in Excel

Course goals • Use a statistical function in an Excel spreadsheet. • Assess which

Course goals • Use a statistical function in an Excel spreadsheet. • Assess which statistical function to use in a particular situation. • Avoid some common errors when using functions. Statistical functions in Excel

Lesson 1 Excel and statistics: The basics Statistical functions in Excel

Lesson 1 Excel and statistics: The basics Statistical functions in Excel

Excel and statistics: The basics Building a formula in Excel using statistical functions is

Excel and statistics: The basics Building a formula in Excel using statistical functions is no more difficult than using any other function. You just have to know how to use Excel functions and know a bit about statistics. Use Excel to manage your statistical data. Statistical functions in Excel

Excel and statistics: The basics Using Excel to do everyday statistical calculations can save

Excel and statistics: The basics Using Excel to do everyday statistical calculations can save you a lot of time and effort. This lesson will explain the basics of why you might want to use Excel, and gives a quick reminder on how to build formulas in Excel. Use Excel to manage your statistical data. Statistical functions in Excel

Why Excel? There are many practical applications of statistics in Excel: • A sales

Why Excel? There are many practical applications of statistics in Excel: • A sales manager might want to project the next quarter's sales (TREND function) Excel statistical functions have many uses. • A teacher might want to grade on a curve based on average scores (AVERAGE, MEDIAN, or even MODE functions) Statistical functions in Excel

Why Excel? • A manufacturer checking product quality might be interested in the range

Why Excel? • A manufacturer checking product quality might be interested in the range of items that fall out of acceptable quality limits (STDEV or VAR functions) Excel statistical functions have many uses. • A market researcher might need to find out how many responses in a survey fell within a range of responses (FREQUENCY function) Statistical functions in Excel

Variance in action Imagine a sales manager looking at the sales figures for three

Variance in action Imagine a sales manager looking at the sales figures for three different salespeople to compare their performances. One of the many statistical functions the manager could use is variance (VAR). Statistical functions in Excel

Variance in action • Variance measures how different the individual values of the data

Variance in action • Variance measures how different the individual values of the data are from one another. • Data with low variance contains values that are identical or similar, such as 6, 7, 6, 6, 7. • Data with high variance contains values that are not similar, such as 598, 1, 134, 5, 92. Statistical functions in Excel

How to construct a statistical formula If you know how to use a function

How to construct a statistical formula If you know how to use a function in Excel, you can use a statistics function. They are written in the same way: 1. Always start with an equal sign (=). Finding the variance based on a range 2. Then the function name. 3. Then the arguments in parentheses. Statistical functions in Excel

When Excel isn't enough If you're doing heavy-duty statistical analysis, Excel might not be

When Excel isn't enough If you're doing heavy-duty statistical analysis, Excel might not be powerful enough for your needs. An example is doing research in a lab. If you do regression analysis, Excel requires that the x values be in a single block (adjacent rows or columns), which might not be convenient for you. Statistical functions in Excel

Suggestions for practice 1. Open exercise 1. 2. Calculate variance by typing cell references.

Suggestions for practice 1. Open exercise 1. 2. Calculate variance by typing cell references. 3. Calculate variance by clicking the cell references. 4. Calculate variance by dragging across a cell range. 5. Interpret the results. Statistical functions in Excel

Lesson 2 Write good formulas Statistical functions in Excel

Lesson 2 Write good formulas Statistical functions in Excel

Write good formulas If Excel is so great for doing statistics, why doesn't everyone

Write good formulas If Excel is so great for doing statistics, why doesn't everyone use it? • Sometimes it's hard to know what Excel can do. • People can get errors when trying to get the results they want. Success with Excel requires good formulas. Statistical functions in Excel

What's the problem? To build better formulas in Excel: • Increase your knowledge of

What's the problem? To build better formulas in Excel: • Increase your knowledge of functions • Avoid the common mistakes that many people make Incorrectly written formulas return error messages. Statistical functions in Excel

What's the problem? The main thing is to use the right function and to

What's the problem? The main thing is to use the right function and to know how to write a proper formula. Using an incorrectly written formula, or the wrong function, usually results in an incorrect answer. Incorrectly written formulas return error messages. Anyone using Excel, not just those using statistical functions, encounters these problems. Statistical functions in Excel

How to solve the problem There are plenty of sources of information to help

How to solve the problem There are plenty of sources of information to help you out. You can learn to write better formulas with such resources as: • This training course • Excel Help topics The Insert Function dialog box • The Insert Function dialog box Statistical functions in Excel

How to solve the problem In the Insert Function dialog box, you can: •

How to solve the problem In the Insert Function dialog box, you can: • Choose which type of function you're looking for • Select a specific function from a list of functions The Insert Function dialog box • Get a description of that function as well as request help on it Statistical functions in Excel

How to solve the problem To open the Insert Function dialog box, click Function

How to solve the problem To open the Insert Function dialog box, click Function on the Insert menu. Then: 1. Pick the function type you need. 2. Find the function name in the list. The Insert Function dialog box 3. Check the function description to make sure you've selected the right one. 4. Click the Help link if you need more information. Statistical functions in Excel

How to solve the problem Note If you can't find the function you're looking

How to solve the problem Note If you can't find the function you're looking for in a particular category, try a related category. For example: The Insert Function dialog box • AVERAGE, the function for arithmetic mean, is listed under the Statistical category, not in Math & Trig, as you might expect. • RAND, the random number function, is listed under Math & Trig. Statistical functions in Excel

The importance of spelling Now take a look at the Normal Distribution, which is

The importance of spelling Now take a look at the Normal Distribution, which is a smooth, symmetrical, bell-shaped curve. The area under the curve up to z shows the probability of getting z. These values are also available in statistics tables. If you measured the height of a sample of plants grown under the same conditions, the distribution of the heights would approximate the Normal curve. Statistical functions in Excel

The importance of spelling There are two functions in Excel for the Normal Distribution:

The importance of spelling There are two functions in Excel for the Normal Distribution: • NORMDIST • NORMSDIST The area under the curve up to z shows the probability of getting z. These values are also available in statistics tables. Statistical functions in Excel

The importance of spelling In the practice session you'll use the NORMSDIST function, which

The importance of spelling In the practice session you'll use the NORMSDIST function, which calculates probabilities associated with the Standard Normal Distribution. The Standard Normal Distribution curve is centered at 0 (the mean). The area under the curve up to z shows the probability of getting z. These values are also available in statistics tables. Statistical functions in Excel

More common problems Some of the most common mistakes when building formulas include: •

More common problems Some of the most common mistakes when building formulas include: • Forgetting the equal sign (=) at the start of the formula. • Inserting a space before the equal sign. Some common errors in spreadsheets • Having your data in the wrong format (for example, as text rather than as numbers). • Selecting the wrong data range. Statistical functions in Excel

More common problems If you have something wrong in a formula, Excel might return

More common problems If you have something wrong in a formula, Excel might return an incorrect result, such as #VALUE!, as shown in the illustration on the left. Some common errors in spreadsheets Statistical functions in Excel

Suggestions for practice 1. Open exercise 2. 2. Troubleshoot some common problems. 3. Use

Suggestions for practice 1. Open exercise 2. 2. Troubleshoot some common problems. 3. Use the Insert Function dialog box. 4. Write a formula. 5. Analyze the results. Statistical functions in Excel

Lesson 3 Which function to use? Statistical functions in Excel

Lesson 3 Which function to use? Statistical functions in Excel

Which function to use? You've mastered writing formulas in Excel. You know to start

Which function to use? You've mastered writing formulas in Excel. You know to start with the equal sign and how to troubleshoot some error messages. What else could possibly cause a problem? Could you still get incorrect results? Statistical functions in Excel

Still have the wrong answer? If you're not sure which function to use, there's

Still have the wrong answer? If you're not sure which function to use, there's only so much help that Excel can give you. You need to be able to analyze your data: even when you think you know which statistical function you want, some statistical knowledge is necessary. The Insert Function dialog box Statistical functions in Excel

Still have the wrong answer? Many similarly named functions in Excel are related to

Still have the wrong answer? Many similarly named functions in Excel are related to one another. If you've picked a function that does something slightly different from what you intended, make sure you've got the right one. The Insert Function dialog box • Check the purpose of each individual function in the Insert Function dialog box and use the link to the Help topics. Statistical functions in Excel

Which function? Another example of a statistical function that has more than one option

Which function? Another example of a statistical function that has more than one option to choose from in Excel is standard deviation. A sample population and an entire population Statistical functions in Excel

Which function? The standard-deviation functions available in Excel are: • STDEV A sample population

Which function? The standard-deviation functions available in Excel are: • STDEV A sample population and an entire population • STDEVA • STDEVPA Statistical functions in Excel

Which function? Function Type of statistical data Type of Excel data STDEV Sample Numeric

Which function? Function Type of statistical data Type of Excel data STDEV Sample Numeric STDEVA Sample Numeric, logical, and text STDEVP Population Numeric STDEVPA Population Numeric, logical, and text STDEVP and STDEVPA both use entire populations, whereas STDEV and STDEVA work on samples of populations. This is a statistical difference. Statistical functions in Excel

Which function? Function Type of statistical data Type of Excel data STDEV Sample Numeric

Which function? Function Type of statistical data Type of Excel data STDEV Sample Numeric STDEVA Sample Numeric, logical, and text STDEVP Population Numeric STDEVPA Population Numeric, logical, and text STDEVA and STDEVPA will recognize TRUE and FALSE text (or logical values) as well as numerals. STDEV and STDEVP will only recognize numeric values. Statistical functions in Excel

Suggestions for practice 1. Open exercise 3. 2. Work with functions that accept logical

Suggestions for practice 1. Open exercise 3. 2. Work with functions that accept logical values. 3. Compare values between entire population functions and sample population functions. Statistical functions in Excel