Excel Errors

In: Computers and Technology

Submitted By mohamadhjameel
Words 1657
Pages 7
2. Spreadsheet Errors: What we think we can do

What we know?

1. What are the leading causes of Spreadsheet errors?

The leading causes of spreadsheet errors are

a) Human incapability of doing complex cognitive tasks with accuracy, this is a limitation in human capability and this contributes partly to the errors that are caused in spreadsheets

b) Lack of disciplined development practices is one of the most common reasons why spreadsheet contains errors. Many organization has policies in general about end user development in general, much less spreadsheet development

c) Many developers build the models and hardwire it to one end result they are working without considering the constraints or different possible scenarios. This ultimately leads to the fact that the model doesn’t work for any other input variables other than the one that is being used to.

d) Another common reason of spreadsheet errors is due to the overconfidence of the developers in the accuracy of the spreadsheets. They don’t spend time to test the spreadsheet for any possible errors

e) We could also say innocence (in a sense of not knowing the excel capabilities) as one of the common reasons why spreadsheets has errors, people tend to learn from friends and coworkers without any proper training on Excel and its capabilities which ultimately lead in unstructured spreadsheet development

2. Why these errors are typically not detected?

a) Primary reason for missing out these errors is that there is no adoption of the standard development cycle phases, many developers directly jump in and do the development without putting any thoughts and scenarios and once the development is done there is very little or no effort put in to test what is developed with different decision parameters.

b) Secondly since there is no standard…...

Similar Documents

Excel Tips

...1 Microsoft Excel 2010 Tutorial Excel is a spreadsheet program in the Microsoft Office system. You can use Excel to create and format workbooks (a collection of spreadsheets) in order to analyze data and make more informed business decisions. Specifically, you can use Excel to track data, build models for analyzing data, write formulas to perform calculations on that data, pivot the data in numerous ways, and present data in a variety of professional looking charts. The Ribbon Understanding the Ribbon is a great way to help understand the changes between Microsoft 2003 to Microsoft 2010. The ribbon holds all of the information in previous versions of Microsoft Office in a more visual stream line manner through a series of tabs that include an immense variety of program features. Home Tab This is the most used tab; it incorporates all text and cell formatting features such as font and paragraph changes. The Home Tab also includes basic spreadsheet formatting elements such as text wrap, merging cells and cell style. Insert Tab This tab allows you to insert a variety of items into a document from pictures, clip art, and headers and footers. Page Layout Tab This tab has commands to adjust page such as margins, orientation and themes. Created By: Amy Beauchemin Source: office.microsoft.com 1/13/11 2 Formulas Tab This tab has commands to use when creating Formulas. This tab holds an immense function library which can assist when creating any formula or......

Words: 7496 - Pages: 30

Excel

...ADVANCED EXCEL TRAINING Quality Management Objective  To provide the participants with an understanding of the advanced features of MS Excel Today is 7/22/2011 2 Advanced  ® EXCEL Topics Working with Data Lists Naming Cells and Ranges   Conditional Functions Lookup Functions   Pivot Tables Data Validation  Today is 7/22/2011 3 Naming Cells and Ranges Today is 7/22/2011 4 Name cells by using labels Name cells by using existing row and column labels Select the range you want to name, including the row or column labels. On In the Insert menu, point to Name, and then click Create. the Create names in box, designate the location that contains the labels by selecting the Top row, Left column, Bottom row, or Right column check box. Note A name created by using this procedure refers only to the cells that contain values and does not include the existing row and column labels. Today is 7/22/2011 5 Name a Cell or Range Using Names Box  1. Select the cell or range you want to name.  2. Click the Name box on the formula bar.  3. Type a name for the range. A range name can include uppercase or lowercase letters, numbers, and punctuation, but no spaces. Try to use a simple name that reflects the type of information in the range, such as BookTitle.  4. Press Enter. The range name will appear in the Name box whenever you select the range. Today is 7/22/2011 6 Conditional......

Words: 1579 - Pages: 7

Excel

...Project Description: You are an analyst for an art gallery that is an authorized Greenwich Workshop® fine art dealer. You prepared a list of artwork by James Christensen and need to organize the data using an outline, subtotals, PivotTables, and PivotCharts to aid you in analyzing the data, which you will do in this project. Instructions: For the purpose of grading the project you are required to perform the following tasks: Step Instructions Points Possible 1 Start Excel. Download, save, and open the file named Exploring_e05_Grader_EOC.xlsx. 0 2 Sort the data on the Subtotals worksheet first by the Type column, then by the Art column, both in alphabetical order. 4 3 Subtotal the data at each change in Type. Identify the highest issue price, estimated value, and percent increase. 10 4 Add an automatic outline to the Subtotals worksheet. Group the first and last name columns. Collapse the names columns, and then collapse columns created by the outline (G, H, and I). 7 5 Create a PivotTable on a new worksheet using the data on the Christensen worksheet in the range A1:J19. Use the Type, Est. Value, and Issue Price fields and use the default locations in the PivotTable Layout. 8 6 Modify the Issue Price and Est. Value values to determine the average issue price and average estimated market value by type. 6 7 Insert a calculated field named % Change in Value to determine the percent change in values by type. Format the values in the new column as percentages......

Words: 312 - Pages: 2

Excel Formulas

...Microsoft Excel 2010 Formulas & Functions  Table of Contents Excel 2010 Formulas & Functions ……………………………………………….….. o Formula Basics ……………………………………………….….. o Order of Operation ……………………………………………….….. Conditional Formatting ……………………………………………….….. Cell Styles ……………………………………………….….. Formulas & Functions ……………………………………………….….. Basic Formulas ……………………………………………….….. o Add the Values in Rows or Columns ……………………………………………….….. o Find the Average, Maximum, or Minimum ……………………………………………….….. Ranges & Individual Cells ……………………………………………….….. Copy a Formula ……………………………………………….….. o Using the Fill Handle ……………………………………………….….. o Using Traditional Copy & Paste ……………………………………………….….. Referencing Cells in Formulas ……………………………………………….….. o Relative References ……………………………………………….….. o Absolute References ……………………………………………….….. o Mixed References ……………………………………………….….. o References to Other Worksheets ……………………………………………….….. o Other Workbook References ……………………………………………….….. Quick Reference Guide ……………………………………………….….. o Add, Subtract, Multiple & Divide ……………………………………………….….. o Understanding Error Values ……………………………………………….….. Practical Purposes of Excel Formulas ……………………………………………….….. o Using Date & Time Formulas ……………………………………………….….. Commonly Used Functions & Formulas ……………………………………………….….. o Math & Trig Icon ……………………………………………….….. Logical Icon ……………………………………………….….. Lookup & Reference Icon ……………………………………………….….. Date & Time Icon ……………………………………………….….. AutoSum Icon ……………………………………………….…........

Words: 478 - Pages: 2

Excel

...Function keys editing in a cell is turned off SHIFT+F2 adds or edits a cell comment. in excel ALT+F1 creates a chart of the data in the current range ALT+SHIFT insert a new worksheet Edits the active cell and positions the insertion point at the end of the cell contents. It also moves the insertion point into the Formula Bar when CTRL+F2 displays the Print Preview window When a cell reference or range is selected in a formula, F4 cycles through the various combinations of absolute and relative references. CTRL+F4 closes the selected workbook window Switches between the worksheet, Ribbon, task pane, and Zoom controls. In a worksheet that has been split (View menu, Manage This Window, Freeze Panes, Split Window command), F6 includes the split panes when switching between panes and the Ribbon area. SHIFT+F6 switches between the worksheet, Zoom controls, task pane, and Ribbon. CTRL+F6 switches to the next workbook window when more than one workbook window is open. Calculates all worksheets in all open workbooks. SHIFT+F9 calculates the active worksheet. CTRL+ALT+F9 calculates all worksheets in all open workbooks, regardless of whether they have changed since the last calculation. CTRL+ALT+SHIFT+F9 rechecks dependent formulas, and then calculates all cells in all open workbooks, including cells not marked as needing to be calculated. CTRL+F9 minimizes a workbook window to an icon. Creates a chart of the data in the current range. SHIFT+F11 inserts a new......

Words: 274 - Pages: 2

Excel

...Microsoft Excel 2010 Formulas & Functions  Table of Contents Excel 2010 Formulas & Functions ……………………………………………….….. o Formula Basics ……………………………………………….….. o Order of Operation ……………………………………………….….. Conditional Formatting ……………………………………………….….. Cell Styles ……………………………………………….….. Formulas & Functions ……………………………………………….….. Basic Formulas ……………………………………………….….. o Add the Values in Rows or Columns ……………………………………………….….. o Find the Average, Maximum, or Minimum ……………………………………………….….. Ranges & Individual Cells ……………………………………………….….. Copy a Formula ……………………………………………….….. o Using the Fill Handle ……………………………………………….….. o Using Traditional Copy & Paste ……………………………………………….….. Referencing Cells in Formulas ……………………………………………….….. o Relative References ……………………………………………….….. o Absolute References ……………………………………………….….. o Mixed References ……………………………………………….….. o References to Other Worksheets ……………………………………………….….. o Other Workbook References ……………………………………………….….. Quick Reference Guide ……………………………………………….….. o Add, Subtract, Multiple & Divide ……………………………………………….….. o Understanding Error Values ……………………………………………….….. Practical Purposes of Excel Formulas ……………………………………………….….. o Using Date & Time Formulas ……………………………………………….….. Commonly Used Functions & Formulas ……………………………………………….….. o Math & Trig Icon ……………………………………………….….. Logical Icon ……………………………………………….….. Lookup & Reference Icon ……………………………………………….….. Date & Time Icon ……………………………………………….….. AutoSum Icon ……………………………………………….…........

Words: 5629 - Pages: 23

Excel

...New Perspectives Excel 2013 Tutorial 3: SAM Project 1a Spring Software ENHANCE A TIMESHEET Project Goal M Project Name Project Goal New Perspectives Excel 2013 Tutorial 3: SAM Project 1a Spring Software ENHANCE A TIMESHEET Project Goal M Project Name Project Goal PROJECT DESCRIPTION Devon Hastings is the human resources director for Spring Software, a software development company in Cambridge, Massachusetts. He has created a workbook for employees to use in recording the hours they work. He would like your help finishing the timesheet. GETTING STARTED * Download the following file from the SAM website: * NP_Excel2013_T3_P1a_FirstLastName_1.xlsx * Open the file you just downloaded and save it with the name: * NP_Excel2013_T3_P1a_FirstLastName_2.xlsx * Hint: If you do not see the .xlsx file extension in the Save file dialog box, do not type it. Excel will add the file extension for you automatically. * With the file NP_Excel2013_T3_P1a_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet. If cell B6 does not display your name, delete the file and download a new copy from the SAM website. PROJECT STEPS 1. On the Employee List worksheet, select cells A1:B1 and then use the Format Painter copy the format only to the range A2:B9. 2. In cell B1 enter bs001, in cell B2 enter cg001, in cell B3 type dh001, and then use Flash......

Words: 790 - Pages: 4

Excel

...G., & Bittner, N. (2010). Nurses’ and nursing assistants’ reports of missed care and delegation. Journal of Nursing Administration, 40(7-8), 329–335. Retrieved from http://journals.lww.com/ jonajournal/Abstract/2010/07000/Nurses__and_Nursing_ Assistants__Reports_of_Missed.8.aspx Hansten, R. (2011). Teaching nurses to delegate and supervise at the point of care: an open request of educators [Letter to the editor]. Nursing Forum, 46(2), 51–53. Retrieved from http://onlinelibrary. wiley.com/doi/10.1111/j.1744-6198.2011.00220.x/abstract Hansten, R., & Jackson, M. (2004). Clinical delegation skills: A handbook for professional practice (3rd ed.). Sudbury, MA: Jones and Bartlett. Institute of Medicine. (2006). Preventing medication errors. Washington, DC: National Academies Press. Kalisch, B. J. (2011). Risk management. The impact of RN-UAP relationships on quality and safety. Nursing Management, 42(9), 16– 22. doi:10.1097/01.NUMA.0000403284.27249.a2 Mitty, E., Resnick, B., Allen, J., Bakerjian, D., Hertz, J., Gardner, W., … Mezey, M. (2010). Nursing delegation and medication administration in assisted living. Nursing Administration Quarterly, 34(2), 162–171. Retrieved from www.ncbi.nlm.nih.gov/ pubmed/20234251 Mueller, C., Anderson, R.A., McConnell, E.S., & Corazzini, K. (2012). Licensed nurse responsibilities in nursing homes: A scope of practice issue. Journal of Nursing Regulation, 3(1), 1-8. Mueller, C., Arling, G., Kane, R., Bershadsky, J., Holland, D....

Words: 5394 - Pages: 22

Excel

...P rate and y = T P rate. Possible values range from (0, 0) to (1, 1). The “classification” that assigns “Test Negative” to every score has FP rate of 0 and TP rate of 0, represented as point (0, 0). The “classification” that assigns “Test Positive” to every value has FP rate of 1 and TP rate of 1, represented as the point (1, 1). Joining all known (x, y) points by straight lines, the area under the resulting empirical ROC curve is known as the Area Under the Curve or AUC. The AUC ranges from 0.5, for a test no better than chance, to 1, for a perfect test. The Area Under the Curve is calculated by the summation of the average height multiplied by the width, as follows: ! ! T P (n) + T P (n + 1) 1 2 (F P (n + 1) F P (n)) See Excel Spreadsheets for examples of calculating each point on an empirical ROC Curve. 2 2.1 2.1.1 Information Measures Probability Review Basic Probability Definitions Joint probability: p(X, Y ) The probability that both X and Y are true. Joint probability is commutative: p(X, Y ) = p(Y, X). Conditional probability: p(X|Y ) The probability that X is true, given that Y is true. Note that “Rates,” PPV and NPV are Conditional Probabilities: True Positive Rate = p(T P T est|+), 3 False Negative Rate = p(F N T est|+), False Positive Rate = p(F P T est| ), True Negative Rate = p(T N T est| ), Positive Predictive Value = p(+|T est P ositive), Negative Predictive Value = p( |T est N egative). Marginal probability:......

Words: 4475 - Pages: 18

Excel

...Applied Statistics Microsoft Excel Decomposition may reduce appeal emphasis on applications. Flow charts help make the analysis decision by identifying technique, computing statistics, and interpreting results. Associated data set accompanies the set purports to build a excel spreadsheet that discovers formula, illustrates the parameters, observes dynamic graphs and simulations that recodes, edits, validate, charts, and automatically links equations, data, and formulas to recalculate the data analysis. Excel recognizes statistical capability conjunction that carries out complex algorithms that implement correlated data columns. Excel quickly analyzes and interprets information filtered into a spreadsheet so the process is much easier then working out these formulas by hand. Excel spreadsheets create charts, graphs, and filters data all into one. Excel uses all of the basic mathematical operations such as addition, subtraction, division, and multiplication. Fractions and decimals are just as common for interpretation data into a excel spreadsheet. An advantage about excel is you can place any formula into the spreadsheet whether in ascending or descending order and using the same formula, it will automatically compute the answer for the next equation. Another advantage is if information is inaccurate, you can go back and check your work with just a click, unlike using y calculations or by hand you have to erase so much and start all over again, which could be time......

Words: 377 - Pages: 2

Excel

...| 29 | 62 |   | 30 | 91 |   |   |   |   | Output Hypothesis Test: Mean vs. Hypothesized Value | | | | | | | | | | | | | | | | 75.000 | hypothesized value | | | | | | | 49.900 | mean # of students who read the newspaper online | | | | 22.441 | std. dev. | | | | | | | | 4.097 | std. error | | | | | | | | 30 | n | | | | | | | | 29 | df | | | | | | | | | | | | | | | | | -6.13 | t | | | | | 0.480319 | | | 1.13E-06 | p-value (two-tailed) | | | | | | * Hypothesis is a method of making decisions using data, whether from a controlled or uncontrolled experiment. The value 75.000 represents the predicted value of students who will read the newspaper online. * Mean; meaning average. The value 49.900 represents the average number of students who read the paper online. * Standard deviation is generally used to measure confidence in statistical conclusions and represents how much variation exists from the expected value. The value 22.441 represents the number of students who are assured to read the newspaper. * Standard error is used to refer to an estimate for standard deviation which is computed from a sample of data being analyzed at the time. The analyzed value is 4.097 students. * N= The length of measurement or time of data collection (30 days) * df: Degree of freedom is the number of values in the final calculation that are permitted......

Words: 329 - Pages: 2

Excel

...Copy the B5 formula to the other product groups in the Forecast column. You can also use the fill feature to copy to the Actual and Difference columns too. i) Format columns B, C, & D in the January, February, March and Summary sheets as Currency with 2 decimals. Format the %Change column as percentage with 2 decimals. The Total Revenue Difference (cell D10 of the Summary sheet) should be $88,188.00 On the Employee Data sheet: a. Set a Data Validation rule (p. EX 438) on the Job Status column for a drop down List of choices CN, FT or PT. You will need to key these in the Source box. Separate each choice by a comma (,). a) Set the Input Message to say Please select CN, FT or PT from the drop down list. b) Set the Error Alert to Stop Style with the message You did not choose a Status from the list. Use the Employee Data sheet: a) In cell I2, create an IF(And function to determine if an employee is eligible for an award. To be eligible, employees must be FT and be employed at least one year. If they are eligible, display “Yes” in column I, if not display “No”. (p. EX 370, use in conjunction with IF ) b) Copy the formula down to all employees (fill in Column I). Again on the Employee Data sheet a) In cell J2, create an IF function with a nested Vlookup (p. EX 387)as the value_if_true to determine the amount of Award money earned. Use column I (Eligible for Award) as the test for “Yes”, then use a Vlookup (with the array in......

Words: 1218 - Pages: 5

Excel

...Terms Definitions The First step in creating an effective workseet is to make sure you __________. Understand what is required In Excel, a number can contain the charaters ________. 0 1 2 3 4 5 6 7 8 9 The cell being copied is called the source area (copy area). The range of cells recieving the copy is called the ________. receiving range You ________ a worksheet to emphasize certain entires and make the worksheet easier to read and understand. Format Pressing the _________ keyboard shorcut key(s) selects cell A1. CTRL + HOME How many chart types does Excel offer? 11 Which of the following keys is an alternative to double-clicking the cell to edit it? F2 Which of the following Opton buttons gives options for filling cells following a fill operation? Auto Fill Options A ________ cell in Excel has a numerical value of zero. Blank The ________ AutoComplete list contains those functiond that akohabetically match the letters you type following thr equal sign. Formula The most popular background color is _______. White ________ text often provides a strong visual appeal. Underlined To open the format cells dialog box with the Alignment sheet active, click the Format Cells ________. Dialog Box Launcher Which of the following is an extension of the fill patten Sunday, Tuesday? Thursday, Saturday, Monday Which of the following formula contains the absolute cell reference? =$B$4 A(n) _______ chart provides a simple way to show trends and variatons in a range of data...

Words: 423 - Pages: 2

Excel Formulas

...Microsoft Excel: Fo rmulas, F or mulas.. . Northern New York Library Network 6721 US Hwy 11 Postdam, NY 13676 877-833-1674 or 315-265-1119 www.nnyln.org Jim Crowley C3 - Crowley Computer Consulting 9148 State Highway 37 Ogdensburg NY 13669 315-394-7008 fax 315-394-7009 www.crowleycomputers.com jim@crowleycomputers.com Description: Excel has hundreds of functions and nobody knows them all, but spend some time and learn to create more powerful formulas. Students will dive into decision making functions like if() and lookup() and make Excel work for them! Students will also explore efficient use of multiple sheets in a workbook, creating multi-sheet and multi-workbook formulas. Students will learn about: if() functions, lookup() functions, conditional formatting, and using multiple worksheets in a workbook. 1. Course objectives. a. To provide the class with an exploration of spreadsheet design. b. To develop computer and spreadsheet literacy. c. To address the class' questions. 2. Schedule a. 9:00 Registration b. 9:30 Morning session c. 12:00 Departure 3. Handouts available in Adobe Portable Document Format (PDF) for download at www.crowleycomputers.com/handouts.htm 1. 2. Formulas: All formulas begin with = or basic math function a. Simple operators Monthly weather (1) + averages.xls (2) (3) * multiply (4) / divide (5) ^ exponent (6) = < > comparison b. Order of operator precedence (or Algebraic Order of Operations, for those of you as old as me!) determines what order...

Words: 1729 - Pages: 7

Microsoft Excel

...Using Microsoft Excel Getting Started Using Microsoft Excel About Excel What is a Spreadsheet? Microsoft Excel is a program that’s used for creating spreadsheets. So what is a spreadsheet? Before personal computers were common, “spreadsheet” referred to large sheets of lined paper, which were used by people in various businesses to record facts and figures in rows and columns, and then make calculations based on the information. When personal computers first began appearing, one of the first applications was a program released in 1979 called VisiCalc. It was used as a tool for performing spreadsheet style calculations that would have been to difficult to do on a calculator. The program quickly became so popular that people began buying personal computers for their businesses just so they could use VisiCalc. VisiCalc Since then, many other spreadsheet programs have been popular over the years, such as Quattro Pro and Lotus 123. Microsoft Excel was first released in 1985 with newer versions being released every couple of years. The most recent version is Excel 2003 (version 11). © Stephen O’Neil 2005 Page 1 Of 17 www.oneil.com.au/pc Using Microsoft Excel Getting Started How Do Spreadsheets Work? Computer spreadsheets are based on their old paper formats. A spreadsheet on a computer uses rows and columns to record information such as text and numbers, such as the example below. Graduates from University 1996 1997 1998 1999 2000 2001......

Words: 4397 - Pages: 18