Joseph Schmuller - Statistical Analysis with Excel For Dummies

Now for another possibility. Suppose you want to know each row total's proportion of the grand total (the number in H11). That should be straightforward, right? Create a formula for I2, and then autofill cells I3 through I10.

Similar to the earlier example, you start by entering this formula into I2:

=H2/H11

Press Enter and the proportion appears in I2. Position the cursor on the fill handle, drag through column I, release in I10, and — d'oh! Figure 1-12 shows the unhappy result — the extremely ugly #/DIV0! in I3 through I10. What's the story?

FIGURE 1-12:Whoops! Incorrect autofill!

The story is this: Unless you tell it not to, Excel uses relative referencing when you autofill. So, the formula inserted into I3 is not

=H3/H11

Instead, it's

=H3/H12

Why does H11 become H12? 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 telling Excel to divide by zero, which is a no-no.

The idea is to tell Excel to divide all numbers by the number in H11, not by “whatever number is nine cells south of here.” To do this, you work with absolute referencing. You show absolute referencing by adding dollar signs ($) to the cell ID. The correct formula for I2 is

= H2/$H$11

This line tells Excel to not adjust the column and to not adjust the row when you autofill. Figure 1-13 shows the worksheet with the proportions, and you can see the correct formula in the formula bar (the area above the worksheet and below the Ribbon).

FIGURE 1-13:Autofill, based on absolute referencing.

To convert a relative reference into absolute reference format, select the cell address (or addresses) you want to convert, press and hold the Fn key, and then press F4. Fn+F4 is a toggle that switches among relative reference (H11, for example), absolute reference for both the row and column in the address ($H$11), absolute reference for the row-part only (H$11), and absolute reference for the column-part only ($H11). You might have to experiment a bit with this — some keyboards only require F4 (without Fn).

A Mac shortcut for this is Command+T.

Here’s how you do it on the iPad. After you enter a formula in this type of context, like

= H2/H11

iPad suspects what you’re up to and highlights the part you might want to work with a bit more — in this case, H11. Tap on that term to pop up a menu. Choose Reference Type from that menu to open the Reference Type menu, shown in Figure 1-14. Tap the desired reference type — in this case, the first one — and then proceed to autofill column I.

FIGURE 1-14:Changing from relative to absolute reference on the iPad.

Chapter 2

IN THIS CHAPTER

Working with worksheet functions

Creating a shortcut to statistical functions

Getting an array of results

Naming arrays

Tooling around with analysis

Analyzing on the iPad

Using Excel’s Quick Statistics feature

In this chapter, I introduce you to Excel's statistical functions and data analysis tools. If you’ve used Excel, and I'm assuming you have, you’re aware of Excel’s extensive functionality, of which statistical capabilities are a subset. You can either enter a piece of data into each worksheet cell, instruct Excel to carry out calculations on data that reside in a set of cells, or use one of Excel’s worksheet functions to work on data. Each worksheet function is a built-in formula that saves you the trouble of having to direct Excel to perform 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.

Many of Excel’s statistical features are built into its worksheet functions. Clicking the Excel Insert Function button (it’s labeled fx ) opens the Insert Function dialog box, which presents a list of Excel’s functions and the capability to search for Excel functions. (On the Mac, this button opens the Formula Builder, which is pretty much the same thing — except it’s a pane rather than a dialog box, meaning that you can keep it open while you work. On the iPad, fx opens a pop-up menu of Excel functions.) Although Excel now provides easier ways to access the worksheet functions, the latest version preserves this button and offers additional ways to open the Insert Function dialog box. I discuss all of this in more detail in a moment.

Figure 2-1 shows the two locations of the Insert Function button. (Note the Formula bar as well.) Along with one Insert Function button, the Formula bar is to the right of the Name box. All three are just below the Ribbon.

FIGURE 2-1:The Function library, the Name box, the Formula bar, the two Insert Function buttons, the Enter button, and the Cancel button.

Near the Name box, just to the left of the Insert Function button, you find an X and a check mark. The X is the Cancel button, and the check mark is the Enter button. Clicking the Enter button is like pressing the Enter key on the keyboard: It tells Excel to perform a computation you’ve typed into a cell. Clicking the Cancel button removes anything you’ve typed into a cell — just as long as you haven’t clicked that Enter button yet. On the iPad, the X and the check mark are to the right of the Formula bar.

Inside the Ribbon, on the Formulas tab, is the Function library. Mac users see a similar layout.

The Formula bar is sort of a clone of any cell you select — information entered into the Formula bar goes into the selected cell, and information entered into the selected cell appears on the Formula bar. You can edit the selected cell’s contents in either the cell or in the Formula bar. The Formula bar provides more room, but the cell can display text at a larger size if you zoom in on the worksheet.