You can think of a standard deviation as an average deviation from the mean. Tables are essentially spreadsheets, or lists, that store database information.
Statistical Analysis with Excel For Dummies, 4th Edition
The tertiary key comes into play only when the primary and secondary keys of records have the same value. See also primary key; secondary key; sort. Many programs export text files, by the way, because other programs including Excel often easily import text files. For example, your name, a budget expense description, and a telephone number are all examples of text labels. None of these pieces of information get used in calculations. A chart that shows sales revenues over the last 5 years or profits over the last 12 months, for example, is a time-series chart.
Tufte, Edward: The author of a series of wonderful books about visually analyzing and visually presenting information. Not coincidentally, Excel provides three T distribution functions. See also z-value. One common probability distribution function is a uniform distribution. For example, the actual amount that you budget for some expense would always be a number or value.
See also formulas; text labels; workbook. The variance is the square of the standard deviation. Conversely, the standard deviation is the square root of the variance. See also average; standard deviation. Excel provides a very slick tool for doing this, by the way. A spreadsheet comprises numbered rows and lettered columns. See also cells. The difference between 60 degrees and 70 degrees is the same as the difference between 80 degrees and 90 degrees. The last data type, ratio data, includes a meaningful zero point. For tempera- tures, the Kelvin scale gives us ratio data.
One hundred degrees Kelvin is twice as hot as 50 degrees Kelvin. This is because the Kelvin zero point is absolute zero, where all molecular motion the basis of heat stops. Another example is a ruler. Eight inches is twice as long as four inches.
Statistical Analysis with Excel For Dummies, 3rd Edition
A length of zero means a complete absence of length. Any of these types can form the basis for an independent variable or a depen- dent variable. A little probability When statisticians make decisions, they express their confidence about those decisions in terms of probability. They can never be certain about what they decide. They can only tell you how probable their conclusions are. So what is probability? The best way to attack this is with a few examples.
Intuitively, you know that if the coin is fair, you have a chance of heads and a chance of tails. How about rolling a die? One member of a pair of dice. You have a standard deck of playing cards. You select one card at random. If you want to know the probability that an event occurs, figure out how many ways that event can happen and divide by 05 ch In each of the three examples, the event we were interested in head, 3, or club only happens one way.
Things can get a bit more complicated. What about the probabil- ity of rolling an even number? On to another kind of probability question. Suppose you roll a die and toss a coin at the same time. Consider all the possible events that could occur when you roll a die and toss a coin at the same time. Your outcome could be a head and , or a tail and The head-and-3 combination can only happen one way.
In general the formula for the probability that a particular event occurs is I began this section by saying that statisticians express their confidence about their decisions in terms of probability, which is really why I brought up this topic in the first place. This line of thinking leads us to conditional probability — the probability that an event occurs given that some other event occurs.
Exactly how does conditional probability plays into statistical analysis? Read on. Inferential Statistics: Testing Hypotheses In advance of doing a study, a statistician draws up a tentative explanation — a hypothesis — as to why the data might come out a certain way. After the study is complete and the sample data are all tabulated, he or she faces the essential decision a statistician has to make — whether or not to reject the hypothesis.
Statistical analysis provides tools to calculate the probability. If the probabil- ity turns out to be low, the statistician rejects the hypothesis. These tosses make up your sample data. The conditional probability of getting 99 heads and 1 tail given a fair coin is very low. Wait a second. The coin could still be fair and you just happened to get a split, right? In fact, you never really know. You have to gather the sample data the results from tosses and make a decision.
Excel Data Analysis For Dummies, 2nd Edition - PDF Drive
Your decision might be right, or it might not. Juries face this all the time. They have to decide among competing hypoth- eses that explain the evidence in a trial. Think of the evidence as data. One hypothesis is that the defendant is guilty. The other is that the defendant is not guilty. The answer to this question deter- mines the verdict. Null and alternative hypotheses Consider once again that coin-tossing study I just mentioned. The sample data are the results from the tosses.
Before tossing the coin, you might start with the hypothesis that the coin is a fair one, so that you expect an equal number of heads and tails. This starting point is called the null hypoth- esis.
- Product description.
- Directory of World Cinema: Spain?
- A Guide to United Kingdom and European Union Competition Policy?
The statistical notation for the null hypothesis is H0. According to this hypothesis, any heads-tails split in the data is consistent with a fair coin. Think of it as the idea that nothing in the results of the study is out of the ordinary. This hypothesis says that any heads-tails split is consistent with an unfair coin.
The alterna- tive hypothesis is called, believe it or not, the alternative hypothesis. The sta- tistical notation for the alternative hypothesis is H1. Similar ideas apply to the reading-speed example I gave earlier. One sample of children receives reading instruction under a new method designed to increase reading speed, the other learns via a traditional method.
Notice also that in the coin-tossing example I said around 50 heads and 50 tails. What about ? Exactly how much different from does the split have to be for you reject H0? In the reading-speed example, how much greater does the improvement have to be to reject H0? Two types of error Whenever you evaluate the data from a study and decide to reject H0 or to not reject H0 , you can never be absolutely sure.
You never really know what the true state of the world is. In the context of the coin-tossing example, that means you never know for certain if the coin is fair or not. All you can do is make a decision based on the sample data you gather.
Shop with confidence
As I mentioned before, the coin could be fair and you just happen to get 99 heads in tosses. They lurk in every study that involves inferential statistics. All you can do is gather more data and see if the additional data are consistent with your decision. If you think of H0 as a tendency to maintain the status quo and not interpret anything as being out of the ordinary no matter how it looks , a Type II error means you missed out on something big.
Looked at in that way, Type II errors form the basis of many historical ironies. The audience voted to determine the winner. The producers held audi- tions around the country to find people for the show. Many years after the show went off the air, the producer was interviewed. We figured this kid would never make it in show business, so we thanked him for showing up, but we sent him on his way. The big news in Excel — throughout Microsoft Office , in fact — is the user interface.
Where a bar of menus once ruled, you now find a tabbed band. Appearing near the top of the worksheet window, this band is called the Ribbon. Figure shows the appearance of the Ribbon after I select the Insert tab. Each tab presents groups of icon-labeled command buttons rather than menu choices. Clicking a button typically opens up a whole category of possibilities.
Buttons that do this are called category buttons. Microsoft has developed shorthand for describing a mouse-click on a com- mand button in the Ribbon, and I use that shorthand throughout this book. I can extend the shorthand. Now you have to reorient: The switch from the menu bar to the Ribbon relocates almost everything. Figure shows a gallery of charts to insert into a worksheet.
- Comets: A Chronological History of Observation, Science, Myth, and Folklore;
- Image-Guided Stereotactic Radiosurgery: High-Precision, Non-invasive Treatment of Solid Tumors.
- Statistical Analysis with Excel for Dummies!
- Statistical Analysis with Excel For Dummies.
- Social Groups in Action and Interaction.
- Implementing Performance-Based Services Acquistion: Perspectives;
- Statistical Analysis with Excel for Dummies by Joseph Schmuller.
- Dead End Deal (1st Edition).
What happened to the Chart Wizard? In keeping with everything-takes-just-a-few-steps-now, to create a chart you 1. Select the data to include in the chart. Insert the chart into the worksheet.
Use the Design tab and the Layout tab to make modifications. Creating a chart is more intuitive than it used to be. Wait another second. Design tab? Layout tab? After you insert a chart and select it, they appear. Tabs that appear when needed are called contextual tabs. Also in keeping with everything-takes-just-a-few-steps-now, to use a statistical function you 1. Select a cell for the result of the function. Select a function from the Statistical Functions menu to open a dialog box for that function. Enter the required information into the dialog box. Close the dialog box. Statistical Functions menu?
Knowing these fundamentals helps you work efficiently with Excel formulas. Insert a formula into a cell, and you can drag that formula into adjoining cells. The data, taken from a U. National Science Foundation report, are in millions of dollars. Column H holds the total for each field, and row 11 holds the total for each year.
More about column I in a moment. I started with column H blank and with row 11 blank. How did I get the totals into column H and row 11? Press Enter and the total appears in H2. When you finish dragging, release the mouse button and the row totals appear. Same thing with the column totals. Another way is to select the array of cells you want to autofill including the one that contains the formula , and click the down arrow next to Home Fill This opens the Fill pop-up menu see Figure Select Down and you accomplish the same thing as dragging and dropping.
Figure The Fill pop-up menu. Still another way is to select Series from the Fill pop-up menu. Doing this opens the Series dialog box see Figure This does take one more step, but the Series dialog box is a bit more compatible with earlier versions of Excel. Figure The Series dialog box. I bring this up because statistical analysis often involves repeating a formula from cell to cell. The formulas are usually more complex than the ones in this section, and you might have to repeat them many times, so it pays to know how to autofill. Referencing cells The second important fundamental is the way Excel references worksheet cells.
Consider again the worksheet in Figure Each autofilled formula is slightly different from the original. This, remember, is the formula in cell H2: 05 ch This is perfectly appropriate. I want the total in each row, so Excel adjusts the formula accordingly as it automatically inserts it into each cell. This is called relative referencing — the reference the cell label gets adjusted rela- tive to where it is in the worksheet.
Here, the formula directs Excel to total up the numbers in the cells in the four columns immediately to the left. Now for another possibility. That should be straightforward, right? Create a formula for I2, and then autofill cells I3 through I Position the cursor on the fill handle, drag through column I, release in I10, and. Figure Whoops! Incorrect autofill!
Relative referencing assumes that the formula means divide the number in the cell by whatever number is nine cells south of here in the same column. Because H12 has nothing in it, the formula is tell- ing Excel to divide by zero, which is a no-no. The idea is to tell Excel to divide all the numbers by the number in H11, not by whatever number is nine cells south of here. To do this, you work with absolute referencing. Figure shows the worksheet with the proportions. Figure Autofill based on absolute referencing. To convert a relative reference into absolute reference format, select the cell address or addresses you want to convert, and press the F4 key.
F4 is a toggle that goes between relative reference H11, for example , absolute 05 ch The instructions in this edition fit in with the steps I outlined in the preceding section.
See Chapter 3. One of my points in both editions is that when you report an average, you should also report variability. Unfortunately, these functions do not exist in Excel See Chapter 5. So I rely much more on named cell ranges in this edition. See Chapter 2. See Chapter Each worksheet function is a built-in formula that saves you the trouble of having to direct Excel to per- form a sequence of calculations.
As newbies and veterans know, formulas are the business end of Excel. The data analysis tools go beyond the formulas. Each tool provides a set of informative results. In previous versions, you accessed the worksheet functions by using the Excel Insert Function button, labeled with the symbol fx. Although Excel provides 06 ch I discuss all of this in more detail in a moment.
Figure shows the location of the Insert Function button and the Formula Bar. All three are just below the Ribbon. Inside the Ribbon, in the Formulas tab, is the Function Library. The Formula Bar is like a clone of a cell you select: Information entered into the Formula Bar goes into the selected cell, and information entered in the selected cell appears in the Formula Bar. Figure shows Excel with the Formulas tab open.
This shows you another location for the Insert Function button. As I mention earlier in this sec- tion, when you click the Insert Function button, you open the Insert Function dialog box. See Figure This dialog box enables you to search for a function that fits your needs, or to scroll through a list of Excel functions. Because of the way earlier versions of Excel were organized, the Insert Function dialog box was extremely useful.
The Function Library presents the categories of formulas you can use and makes it convenient for you to access them. Clicking a category button in this area opens a menu of the functions in that category. Most of the time, I work with Statistical Functions that are easily accessible through the Statistical Functions menu. You see a couple of these later in the chapter. In Chapter 5, I work with a couple of Logic functions. The final selection of each category menu like the Statistical Functions menu is called Insert Function.
Selecting this option is still another way to open the Insert Function dialog box. The Name Box is something like a running record of what you do in the work- sheet. Click the Insert Function button and the name of the function you selected most recently appears in the Name Box. Setting Up for Statistics In this section, I show you how to use the worksheet functions and the analy- sis tools.
As I point out in the preceding section, the Function Library area of the Formulas tab shows all the categories of worksheet functions. The steps in using a worksheet function are: 1. Type your data into a data array and select a cell for the result. Select the appropriate formula category and choose your function from its pop-up menu. Doing this opens the Function Arguments dialog box. Argument is a term from mathematics. It has nothing to do with debates, fights, or confrontations.
In mathematics, an argument is a value on which a function does its work. Click OK to put the result into the selected cell. This function, SUM, adds up the numbers in cells you specify and returns the sum in still another cell that you specify. Although adding numbers together is an integral part of statistical number crunching, SUM is not in the Statistical category. It is, however, a typical worksheet func- tion and it shows a familiar operation.