CGS2518 REVIEW EXAM 1 CHAPTER 1 1 WHAT IS THE MEANING OF EACH OF THESE ERROR MESSAGES a Insufficient width in cell to display numerical data or formulas that result in negative date time b NAME Unrecognized text in a formula c N A No answer d REF Invalid cell reference e VALUE Wrong argument type or operand f NUM Invalid numeric values in a formula or function g DIV 0 Division by zero 2 IF YOU ENTER 1 149 25 IN A CELL EXACTLY AS SHOWN WHAT VALUE WOULD RESULT 1 149 25 3 IF YOU ENTER 2 4 10 IN A CELL EXACTLY AS SHOWN WHAT VALUE WOULD RESULT It would result in the value of 42 being displayed Excel will multiply 4 times 10 and then add 2 4 LIST EACH OF THE FOLLOWING OPERATIONS IN ORDER OF PRECEDENCE FROM 1 TO 4 first to last MULTIPLICATION AND DIVISION 3RD PARENTHESES 1ST ADDITION AND SUBTRACTION 4TH EXPONENTIATION 2ND 5 WHEN WRITING FORMULAS WHY IS IT PREFERABLE TO USE CELL REFERENCES RATHER THAN TYPING IN VALUES It allows the user to easily update the spreadsheet without having to know exactly which formula or formulas contain the changed value 6 IN THE WORKSHEET BELOW CELL A3 CONTAINS THE FORMULA A1 A2 EXPLAIN THE MOST LIKELY REASON THE VALUE CALCULATED APPEARS INCORRECT C B 10 A 1 2 4 1 2 3 4 The issue in this situation is due to rounding In this case excel shows values with 0 decimals It is an issue of Display format 7 REFERING TO THE PRECEDING WORKSHEET IF YOU WROTE THE FORUMLA B1 110 WHAT VALUE WOULD RESULT assuming the display value is the precise value In this case the resulting value would be 10 of 110 so 11 8 WHAT FORMULA WOULD YOU WRITE TO DO EACH OF THE FOLLOWING Add a range of numbers in cells A2 X2 SUM A2 X2 MAX C2 C8 Find the largest value in cells C2 C8 MIN C2 Z12 Find the smallest value in cells B2 through Z12 Find the average value in cells C1 through C10 assuming blank cells will be ignored Find the total number of values listed in cells C1 through C10 excluding any that contain text AVERAGE C2 C10 COUNT C2 C10 9 DEFINE SYNTAX Specifies the function name and order of arguments ARGUMENTS Name given to the function inputs ALGORITHM Name given to the set of rules programed into a function 10 IF THE FORMULA B 4 SUM C1 C5 IS COPIED FROM CELL A9 TO CELL C10 WHAT IS THE RESULTING FORMULA The formula would be the same B 4 SUM C1 C5 11 WHAT FORMULA WOULD YOU WRITE IN CELL B2 THAT CAN BE COPIED DOWN A COLUMN AND ACROSS THE ROW TO COMPLETE THE MULTIPLICATION TABLE A B 1 1 2 3 4 5 C 2 2 4 6 8 10 D 3 3 6 9 12 15 1 2 3 4 5 1 2 3 4 5 6 This formula will make the multiplication table work B 1 A2 F 5 5 10 15 20 25 E 4 4 8 12 16 20 12 WHAT NEW FORMULA RESULTS FOR EACH OF THE FOLLOWING IF THE FORMULA IS COPIED FROM CELL C10 TO CELL E13 C1 C2 because we are not using absolute A reference B C D A1 A2 A 1 A2 A 1 C2 A1 C2 A1 A2 C 1 C2 A 1 A2 13 WHAT FORMULA COULD YOU USE TO ADD UP CELL B1 FROM SHEET 1 SHEET2 AND SHEET 3 assuming the worksheets are contiguous and in the same workbook B1 Sheet2 B1 Sheet3 B1 14 CELL B1 HAS BEEN GIVEN THE RANGE NAME DISCOUNT HOW WOULD YOU WRITE A FORMULA IN CELL C1 THAT MULTIPLIES DISCOUNT BY CELL A1 Discount A1 WHAT NEW FORMULA RESULTS IF YOU COPY THIS SAME FORMULA INTO CELL C2 Discount B1 CHAPTER 2 1 WHAT FORMULA COULD YOU WRITE TO CALCULATE THE MEAN OF THE FOLLOWING DATA SET 2 5 4 3 1 2 7 AVERAGE A1 A7 where the range A1 A7 displays the values 2 WHAT IS THE MEDIAN VALUE OF THE DATA SET GIVEN IN Q1 MEDIAN A1 A7 which equals 3 3 WHAT IS THE MODE OF THE DATA SET GIVEN IN Q1 MODE A1 A7 which equals 2 4 DATA SET IN Q1 HAS STD DEV OF 1 58 ANOTHER DATA SET HAS STD DEV OF 2 5 WHAT GENERAL DIFFERENCES WOULD YOU EXPECT TO FIND BETWEEN THE TWO SETS OF DATA A wide range of values 5 DETERIME AN ALGEBRAIC EQUATION TO EXPRESS THE PERECENT INCREASE IN LABOR COSTS ORIGINAL COST WAS 35 NEW COST IS 37 5 Calculating percent differences New value Historical value Historical value so 37 5 35 35 2 5 35 0 07 or 7 increase in labor costs 6 WHEN USING THE INCREASE DECIMAL BUTTON ON THE TOOLBAR THE PRECISE VALUE IN THE CELL IS MODIFIED T OR F FALSE adjusting the number of decimal places using this tool simply alters the cell display but has no effect on the precision of the value stored 7 THE FORMULA ROUND 345 43 0 RESULTS IN WHAT PRECISE VALUE Results in the value 354 8 WRITE A FORMULA TO ROUND UP 63 34 TO THE NEAREST PERCENT ROUNDUP 63 34 2 9 WHAT IS THE SYMBOL FOR GREATER THAN OR EQUAL TO RELATIONAL OPERATOR IN EXCEL Greater than symbol is Equal to symbol is 10 WHAT IS THE SYMBOL FOR THE NOT EQUAL TO RELATIONAL OPERATOR IN EXCEL Not equal to symbol is 11 REVIEW THE FOLLOWING WORKSHEET AND THEN USE THE COUNTIF FUNCTION TO WRITE A FORMULA THAT DETERMINES THE NUMBER OF GM CARS ON THE LIST A Make Price B 1 Ford 2 GM 3 GM 4 Ford 5 Honda 6 Ford 7 Toyota 8 9 Lexus 10 Nissan 11 TOTAL COUNTIF A2 A10 GM 15 837 12 883 21 210 27 837 20 432 24 552 21 553 32 412 23 134 199 850 12 USING THE WORKSHEET SHOWN IN Q11 WRITE A FORMULA TO DETERMINE THE NUMBER OF CARS THAT COST LESS THAN 20 000 COUNTIF B2 B11 20 000 13 USING THE WORKSHEET SHOWN IN Q11 WRITE A FORMULA TO DETERMINE THE TOTAL VALUE OF ALL FORD CARS SUMIF A2 A10 Ford B2 B10 14 EXPLAIN THE DIFFRENCE BETWEEN A what if ANALYSIS AND GOAL SEEK BY GIVING AN EXAMPLE BASED ON THE WORKSHEET SHOW IN Q11 Using a what if analysis means simply to determine the outcome of changing one or more input values and evaluate the recalculated results Ex changing the cost of labor from 35 per hour to 47 per hour Goal seek in excel enables you to choose an outcome and which input you want to change and excel automatically calculates the solution Ex reach a specific total cost to inspect with the hourly rate changing 15 USING THE WORKSHEET SHOWN IN Q11 WRITE A FORMULA TO DETERMINE THE TOTAL VALUE OF THE THIRD MOST EXPENSIVE CAR LARGE B2 B10 3 16 IF EACH CAR SHOWN IN Q11 IS MARKED UP BETWEEN 50 TO 250 IN DOLLAR INCREMENTS WHAT …
View Full Document
Unlocking...