Rapid response systems



Introduction to database creation and analysis using Microsoft Excel.Introduction, Database creation, and Making sense of data - examples of different scenarios on MS Excel -- also covered in written tutorialOrientation and basic navigation HYPERLINK "" math functions toolbars formulas for RRT data handling with formulas and examples (see accompanying Excel Spreadsheet)Some introductory material is covered, starting on the next pageOrientation to terms and a tour of functionsCell =A small individual box on the spreadsheetCell name =The location of the cell, using a row number and column letter (for example "D2")Range =A group of cells; in formulas, theses are designated with a colon A2:A76This tutorial1. Basic commands2. Basic math operations3. Customizing your menus for greater efficiencyBasic commands and definitions?Command key (Mac) functions same as the Control key (PC/ windows)? while clicking or dragging selects individual cells or a range of cells?Aselect all?Ccopy what is selected?Xcuts?Vpastes what is cut or copied?Nnew worksheet or document?Ssaves your work?1format selection?ZUndo last action (s)Pasting: There are three basic pasting scenarios you should be familiar with1. copying and pasting formulas2. copying and pasting number values of cells (whether created by a formula or not)3. transposingSorting:? + Shift + R(Mac) Takes you to custom sort windowAlt-A-S-S(PC) custom sort windowcommandBasic math operations+=B2+C2- =B2-C2*=B2*C2/=B2/C2( )for logical sequences=(B2+C2)/D2=((B2+C2)/D2) - 15=((B2+C2)/D2) - F17Adding contents of a column:=sum(B2:B2500)or row:=sum(B2:Z2)or a block of values:=sum(B2:Z2500)You can type in the formulas (case insensitive) or click boxes. At different times, one may be more convenient than the other.Formulas vs. numbersIf you see a number in a given cell (cell A3 has a '2') it could come from someone entering a '2' into that cell or from a formula entered in there such as '10-8' or '100/50' etc. Also it could result from basic math operations using cell names such as 'B2' or 'F16' , etc.For example, pretend there are numbers in cells B8, B9, B10. You can type in various formulas based on these three numbers, and what you see in rows 12 through 23 are numbers resulting from formulas typed into the cells. The middle column here shows the formulas used. Thus additions, subtraction, multiplication and division use common symbols for these operations, and brackets are used to separate logical operations.BBC888?91515?1033?11???Addition 1226?=SUM(B7:B9)?13???Subtracting 147?=B8-B7?Complex subtraction1585=100-B8=100-C816???Multiplication 1745?=B8*B9?Complex multiplication 1874=B9*(B8-B7)?19???Dividing 200.53?=B7/B8?Complex division 210.2?=B9/(B8+B10)=C9/(C8+C10)22??=B10/(B9+B11)?Average of all 238.67?=AVERAGE(B7:B9)?Finally, the entries in column C and in B22 show what happens when you copy a cell in column B and paste it into column C. Also B22 shows what happens when you copy a formula from a cell and move it vertically. In B21, you are telling the program to "go up 12 cells and divide that number by the sum of the value from 13 cells above plus the value found 11 cells above." When this formula is copied and pasted one cell below, the formula described in italics above is simply moved down one cell. Same thing for C21: everything was moved to the right one column. Compare this to the formula in C15: the formula here is 100 minus whatever is 7 cells up. Note that the number 100 doesn't change when copied and moved. The beauty of copying formulas is that is you are doing large volume calculations, all you have to do is set up the first cell in a row or column, then paste the formulas across a large swath of cells.Anchoring components of a formulaThis discussion takes off from the dilemma of moving around formulas from place to place on the spreadsheet. Example: If you start with this formula =B2/C2 in cell D2, and copy it across the row (ie to cells E2, F2, and so on), the program will divide the contents of the column one place to the left with the contents of the column two places to the left. This is literally how the computer interprets "D2 = C2/B2." So it follows that E2 will equal D2/C2. But what if you want to divide everything by B2 as you replicate the formula across row #2? In other words you want to have this happen:E2=D2/B2F2=E2/ B2G2=F2/B2What you can do is set up the following formula in D2:" =C2/$B2 "The "$" sign here "anchors" the denominator to column B. When you copy and paste this formula across the row you will have all columns one place to the left divided by B2. When you paste that formula in cells E through G, this is what will appear in the formula bar:E2=D2/$B2F2=E2/ $B2G2=F2/$B2So what happens if you want to replicate this same pattern in row 3? Well, if you copy Cell D2 (=C2/$B2) and paste into cells D3, D4, D5 and on down, the 2s will change to 3s since you only anchored the "B" in the original formula. So now you would get D3=C3/$B3 and so on. . . If you want all values anchored to the contents of B2 (and not what is in column B for a given row), then you have to "anchor" both the "B" and "2" in the following manner. D2=C2/$B$2. Then wherever you copy and paste this formula, the denominator will always be cell B2!Thus, the dollar sign ($) anchors part of a formula to a given component of a cell's column-row location. The anchor can be applied to the row number, the column letter, or both.Customize your menuFor Macs, you will want to customize the "Formatting Toolbar." See photo on next page. Go to View then Toolbars, and select Formatting. Then go back to View/ Toolbars and select the Customize. . . option. When the menu opens, you can find different functions by scrolling through the show all commands menus, then dragging your selections to a good position on your toolbar. Also, you can get rid of the garbage you never use by clicking the commands and dragging them off of the toolbar.For PCs, you will be working with the "quick access tool bar." This is the small toolbar at the top of the program window. See the picture below. Click the down arrow on the quick access bar, and this will open up a selection menu similar to the one described for Macs.Reason for this discussion is that there are a few key commands that you will use frequently and will want easy access to:* Paste values* Fill in color* Replace values*Text color* Delete cells* Border lines* Clear all * Magnification* Center, Middle and right Alignment* Sorting menuOn a Macintosh-34290012573000-11430066294000On a PC ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download