Statistical Analysis with Excel For Dummies. Joseph Schmuller

the Headers box, you can choose either Name in the first row or no headers.For the next few steps, keep in mind that the tool you use determines the number of Variables boxes you work with.

      7 To add an array to a Variables box, select it on the spreadsheet and then tap the little three-cells-and-a-plus-sign icon for the appropriate Variables box.This step opens the Variables pane.

      8 Tap Add in that pane to return to the previous pane.The selected range is in the Variables box.

      9 Tap Run.StatPlus opens a new worksheet with the results.

Figure 2-29 shows the StatPlus pane after Steps 1–5 for the descriptive statistics example in the preceding section.

Figure 2-30 shows the results of the analysis. Notice that the title at the top shows the mean plus-or-minus the standard deviation.

      FIGURE 2-29: The StatPlus pane, with entered values for a Descriptive Statistics analysis.

FIGURE 2-30: The results of the Descriptive Statistics analysis — compare with the results shown in Figure 2-28.

Accessing Commonly Used Functions

      Need quick access to a few commonly used statistical functions? You can get to AVERAGE, MIN (minimum value in a selected cell range) and MAX (maximum value in a selected range) by clicking the down arrow next to the AutoSum button on the left side of the Formulas tab. Clicking this down arrow also gets you to the Mathematical functions SUM and COUNT NUMBERS (counts the numerical values in a cell range).

The AutoSum button is labeled ∑. Figure 2-31 shows you not only exactly where it is but also the menu opened by its down arrow. For some reason, it's in the Editing area of the Home tab. Another AutoSum button (with the same menu) is in the Function library of the Formulas tab.

      FIGURE 2-31: The Home ∑ button and the menu that clicking its down arrow opens.

      By the way, if you just click the ∑ button and not the down arrow, you get SUM.

      The last selection on that menu is yet another way to open the Insert Function dialog box.

      

One nice thing about using the ∑ menu: It eliminates a step. When you select a function, you don’t have to select a cell for the result — just select the cell range, and the function inserts the value in a cell immediately after the range.

The New Analyze Data Tool

The Analyze Data tool is a new feature for Windows and Mac. In your worksheet, select cells with data and then choose Home | Analyze Data to open the Analyze Data pane. Figure 2-32 shows the pane after I selected the data in an example from Chapter 1. (I removed the totals and proportions, although that’s not necessary.)

      The pane offers a number of suggestions, including charts (Excel’s name for graphs) to visualize the data as well as a possible correlation between data in two of the columns (the years 1990 and 2001). I have more to say about charts in Chapter 3. (Correlation? What’s that? Chapter 15 tells you all about it.)

      FIGURE 2-32: The Analyze Data tool at work.

Data from Pictures!

      This new feature is for non-Windows only. (About time, eh?) Here, I describe the iPad version. You use the iPad camera to take a picture of a data table that appears on a printed page and then, through the magic of OCR (optical character recognition), Excel extracts the data in the picture and puts it into a spreadsheet.

      To start collecting data, follow these steps on your iPad:

      1 Find a printed page that presents a data table.For this exercise, I use Table 14-2 of this book.

      2 Flatten out the page as much as possible.This increases the OCR’s accuracy.

      3 Select a cell in the spreadsheet where you want the data table to begin.

      4 Choose Insert | Data from Picture to activate the iPad camera.

      5 Take a picture of the data table. Important advice: I hold the iPad in Portrait mode so that the white button is at the bottom of the screen. If I hold it in Landscape mode (which is probably the way you hold your iPad when you use it for Excel), I end up with gobbledygook. Trust me on this one.

      6 Adjust the white rectangle to crop out everything but the data.This table has some statistics calculated in the bottom rows. In this example, I crop out those rows and then recalculate the statistics after the data are in the spreadsheet. It’s a helpful check on the accuracy of the OCR — if the recalculated statistics don’t match up with the printed version, something is obviously amiss.

      7 Tap Confirm.After a few seconds, the image of the table appears in the upper half of the screen; and the data as it will appear in Excel, in the lower half. Figure 2-33 shows the inaccuracy of the OCR with respect to decimals. You can tap the X in the upper left corner to discard the picture and start again, if you like.FIGURE 2-33: An intermediate result of Data from Picture: The image of the data and the data as it will appear in Excel.

      8 When you’re satisfied with the data table, tap Insert.If the accuracy isn’t perfect (and it rarely is), two buttons appear — one labeled Open Anyway and one labeled Review. Review is more of a hassle than it’s worth — it’s easier to review and make corrections after the numbers are in the spreadsheet. (See Step 9.) My rule of thumb: If the cells are properly aligned and most of the data looks okay, tap Open Anyway to put the data in the spreadsheet. If not, tap the X in the upper left corner and restart the procedure.

      9 Compare the data in the spreadsheet with the data on the printed page. Correct as necessary.Do not omit this step. I pay close attention to decimals because the OCR sometimes misses the decimal point. As I point out in Step 5, recalculating statistics is a helpful check of the OCR accuracy.

      In the Mac version of Data from Picture, you take the picture with an iPhone configured to use Continuity Camera; the option is called Picture from Clipboard. The Mac also gives you the option to choose a picture from your files.

Part 2

      Describing Data

IN THIS PART …

       Summarize and describe data

       Work