Unformatted text preview:

Appendix BExcel ErrorsUnder certain circumstances, even the best formulas can appear to have freaked out once youget them in your worksheet. You can tell right away that a formula’s gone haywire becauseinstead of the nice calculated value you expected to see in the cell, you get a strange,incomprehensible message in all uppercase letters beginning with the number sign (#) andending with an exclamation point (!) or, in one case, a question mark (?). This weirdnessis known, in the parlance of spreadsheets, as an error value. Its purpose is to let you knowthat some element - either in the formula itself or in a cell referred to by the formula - ispreventing Excel from returning the anticipated calculated value.Here is a list of some error values and their meanings:#DIV/0! Appears when the formula calls for division by a cell that either contains thevalue 0 or, as is more often the case, is empty. Division by zero is a no-no accordingto our mathematical rules (you can divide a pizza into 2 slices, but you cannot dividea pizza into zero slices).#NAME? Appears when the formula refers to a range name that doesn’t exist in theworksheet. This error value appears when you type the wrong range name or fail toenclose in quotation marks some text used in the formula, causing Excel to think thatthe text refers to a range name.#NULL! Appears most often when you insert a space (where you should have used acomma) to separate cell references used as arguments for functions.#NUM! Appears when Excel encounters a problem with a number in the formula, suchas the wrong type of argument in an Excel function or a calculation that produces anumber too large or too small to be represented in the worksheet.#REF! Appears when Excel encounters an invalid cell reference, such as when you deletea cell referred to in a formula or paste cells over the cells referred to in a formula.#VALUE! Appears when you use the wrong type of argument or operator in a function,or when you call for a mathematical operation that refers to cells that contain textentries.527528 APPENDIX B. EXCEL ERRORSAppendix COther Features of ExcelWorksheet TabsEach workbook can contain multiple worksheets. For example, you may want a workbookcontaining data on your stock portfolio. The workbook could contain a separate sheetfor each of the stocks in your portfolio showing past data and current information. Oneworksheet could contain a summary of all the current stock information, showing the currenttotal value of your portfolio. To work with multiple sheets, use the worksheet tabs at thebottom of the data entry area. New workbooks contain three worksheets by default.Simply click on the tab for the worksheet you wish to view. If there are too manyworksheets to display all of them, use the arrow keys to move forward in the list (rightarrows) or backward in the list (left arrows). The arrows with vertical bars take you to thefirst worksheet in the list (left arrow with bar) or the last worksheet in the list (right arrowwith bar).To add another worksheet either use the ”Insert” menu, or right-click on the worksheettabs and select ”Add worksheet”. You can also click on the worksheet tab to the right ofthe last worksheet in the workbook. To change the order of the worksheets, click and dragone of the tabs to a new place in the list; you will see a small sheet icon and a down arrowshowing you where the sheet will be placed. It is also helpful to rename the worksheets withmore meaningful names than ”Sheet1” and ”Sheet2”. To do this, either1. Right-click on the tab for the worksheet you want to rename. Select ”Rename” fromthe pop-up menu, then type the new name over the highlighted text.2. Double-click on the name of the sheet you wish to modify, then type the new nameover the highlighted text.Special KeysThere are several special key strokes that you will want to use on a regular basis. To displaythe actual formulas in each cell, rather than the results of the formulas, use CTRL + ‘. The‘ (tilde) key is on the upper left of most keyboards. Hitting this key combination again willswitch back to displaying the results of the formulas.529530 APPENDIX C. OTHER FEATURES OF EXCELTo use the keyboard to move around the worksheet:• The arrow keys move the active cell in the obvious directions.• Holding down SHIFT while navigating with an arrow key will both move the cursorand select (highlight) the cells as you move.• Holding down CTRL while navigating with an arrow key will jump to the last cellcontaining data in that direction. If there is a blank cell in between blocks of data,CTRL + arrow will stop at the last cell before the blank cells begin.• Holding down CTRL + SHIFT and using the arrow keys combines both of the lasttwo features. This is especially useful for selecting a large block of data that scrolls onfor many screens.Appendix DSample Rubric for Evaluating Memo 7The rubric below provides a sample of how instructors can easily use a checklist approachto grading the memo assignments in this text. Bascially, each memo has three categories inwhich students should demonstrate excellence: Mechanics and Techniques, Application andReasoning, and Communication and Professionalism. These are discussed in general termsin the preface. For each category, there are items listed in a checklist format at two levelsof accomplishment: Expected and Impressive. To meet the minimum requirements for amemo, students should have the expected items checked off as being present in the memo ina clear and easily understandable way. Then, for each category in which the student’s workis impressive, the grade is bumped up.For example, one could define the following grade scale for memo problems, where theentries define the number of categories (out of 3) that must be at that level. Then, theintermediate and lower grades (D, C+, etc.) can be awarded for partial success in a category.Grade Expected ImpressiveC 3 0B- 2 1B+ 1 2A 0 3531532 APPENDIX D. SAMPLE RUBRIC FOR EVALUATING MEMO 7Expected Level Impressive LevelM & T: 0 E- E E+ I- I File was correctly titled for Bb File name correct Contains a correlation matrix Contains 4 scatterplotsA Table of Results contains 4 correct regression equations A correct R2for each equation A correct Sefor each equation Correctly states which relationship has thestrongest positive or negative correlation The 4 regression equations are correctlyranked according to best-fit


View Full Document

SJFC MSTI 130 - Excel Errors

Download Excel Errors
Our administrator received your request to download this document. We will send you the file to your email shortly.
Loading Unlocking...
Login

Join to view Excel Errors and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view Excel Errors 2 2 and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?