SEE ATTACHED
Instructions CEVA CLL - Excel Assessment 1 Tool Date Full Name Please rate your Excel skill level by selecting a number below and filing the appropriate cell with light blue 12345 NoviceAdvanced BeginnerCompetentProficientExpert Instructions Answer the multiple choice questions by selecting the corect answers Using the data found in the "Data" tab, answer the other 5 questions in tabs titled "Question #" Multiple Choice Multiple Choices 1Which of the following is a cell reference? C15 5C CC 65,481 2Which Excel feature can summarize data in a large table? Data Validation Conditional Formatting AutoFormat Pivot Table Hyperlink 3The sales tax rate is in cell H2. Sales totals are in cells B2:B20.To calculate the tax on each sale, you'll enter a formula in cell C2, to multiply the sales total in B2, by the sales tax rate. Then, you'll copy the formula down to cell B20.Which formula should you use? =B2*$H$2 =$B$2*$H$2 =B$2*$H$2 =B2*H2 =B2*$H2 4Which of the following formulas could you use to add a range of cells together? SUM() =(SUM) =+SUM() =SUM() 5When number signs (#####) are displayed in the cell, it means: The cell is not wide enough to display the contents. The formula is wrong. The decimal point is wrong. All of the above. 6What function offers you the possibility to view different results depending on the entered condition? =IF() =SUM() =SUMIF() 7Which formula can add all the numeric values in a range of cells, ignoring those which are not numeric, and place the result in a different cell? Count Average Sum 8The cell reference for a range of cells that starts in cell B1 and goes over to column G and down to row 10 is _____. B1-G10 B1.G10 B1;G10 B1:G10 9To select several cells or ranges that are not touching each other, you would _____ while selecting Hold down the CTRL key Hold down the SHIFT key Hold down the ALT key 10In the following formula: =IF(A1="YES","DONE","RESTART"), what happens if A1="NO"? Formula returns the value "DONE" Formula will show an error Formula returns the value "RESTART" Formula returns the value "YES" 11In "=SUM(number1 [,number2 ,number3 ...])" number 1, number 2, and number 3 may be numbers or __________. Letters Cell references Symbols Operators 12When creating an Excel formula, what does the cell reference with dollar signs like $A$1 do? Changes the cell format to currency with a dollar sign as the default Allows only numbers to be input into the cell Generates an error Creates an absolute reference 13Which of the following formulas would find the sum of the cells in column J between rows 1 and 8 in an Excel spreadsheet? =ADD(J1-J8) =SUM(J1:J8) =J:1+J:8 =SUM J:1-8 14A Logical test in Excel can contain =, >=, <=,>,<,><>, $ All Except $ All Except <> All Except = All Except >= 15In an IF Function the required arguments are ____________. The Logical test and value if test is true, value if test is false The Logical test and the three possible outcomes The Logical test plus initial outcome plus true and false Logical test and value if true, value if test is false plus formatting options 16Which of the following functions is correctly written? =IF(B2>50000,"rich","poor") =IF(B2>50000."rich","poor") IF(B2>50000,rich,poor) =IF(B2>50000),"rich","poor")) 17What function would you use to display today's date so that tomorrow it will update automatically =TODAY() =[date] =[system date] =datevalue() 18What is the purpose of range lookup in VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)? To identify exact matches To identify closest min value matches To identify closest max value matches To identify positive values 19What does SUMIFS function do? Add up cell values based on a condition Add all the numbers in a range of cells Returns a subtotal in a list or database All of Above 20What is the correct formula to calculate an average between cells B3 and B6? =SUM(B3+B4+B5+B6) =AVE(B3:B6) =AVERAGE(B3:B6) =AVERAGE(B3+B4+B5+B6)/4 =AVE(B3+B4+B5+B6)/4 21=Sheet1!D9+Sheet1!E9 This would select D9 and E9 and create an average from sheet2 This would select D9 and E9 and add the two together from sheet2 This would select D9 and E9 and add the two together from sheet1 This formula doesn't do anything 22To record a sequence of keystrokes and mouse actions to play back later we use: Media Player Sound Recorder Calculator Macro Recorder 23The Auto-Fill Handle can be used to: Copy formulas Copy text Fill in a series of numbers or dates All of the Above None of the Above 24The formula that will add D4 to the product of B1 and B2 is D4 + (B1+B2) =D4+(B1*B2) D4+B1*B2 =D4+(B1 + B2) 25What formula is used to combine several text or cell references into one cell? =CONSOLIDATE() =CONCATENATE() =TRIM() =MID() Question 1 Question 1 Using the data located in the "Data" tab, find: A) Total weight of all shipments B) Average weight of all shipments Please leave your formulas to show your work. Do not copy and paste values. Total Weight A B Question 2 Question 2 Using the data in the "Data" tab, perform a VLOOKUP in C6 to find the weight of the shipment going to Plant E. Destination NameWeight Plant E Question 3 Question 3 In Column M of the table below, concatenate the Origin Name and Destination Name so that the result looks like: "Origin Name - Destination Name" Origin NameOrigin CityOrigin StateWeightPiecesDestination NameDestination CityDestination StateCarrierScheduled ArrivalETAOrigin - Destination Supplier ABETHLEHEMPA1187Plant ALaredoTXCarrier A3/9/2018 4:00 PM3/12/2018 5:00 PM Supplier BTorontoON200Plant BBEREAKYCarrier B3/8/2018 4:00 PM3/12/2018 5:00 PM Supplier CLaredoTX345Plant CBUCHANANMICarrier C3/9/2018 4:00 PM3/12/2018 5:00 PM Supplier DBreaCA5900Plant CBUCHANANMICarrier C3/12/2018 4:00 PM3/12/2018 5:00 PM Supplier EChicagoIL2406Plant BBEREAKYCarrier B3/12/2018 4:00 PM3/12/2018 5:00 PM Supplier FPigeonMI12346Plant ALaredoTXCarrier A3/9/2018 4:00 PM3/12/2018 5:00 PM Supplier BTorontoON1400Plant BBEREAKYCarrier B3/12/2018 4:00 PM3/12/2018 5:00 PM Supplier BTorontoON7523Plant DGreenvilleNCCarrier B3/12/2018 4:00 PM3/12/2018 5:00 PM Supplier EChicagoIL134Plant ALaredoTXCarrier A3/9/2018 4:00 PM3/12/2018 5:00 PM Supplier GFrankfortKY1760Plant DGreenvilleNCCarrier B3/8/2018 4:00 PM3/12/2018 5:00 PM Supplier BTorontoON3720Plant BBEREAKYCarrier B3/12/2018 4:00 PM3/12/2018 5:00 PM Supplier DBreaCA230Plant CBUCHANANMICarrier C3/12/2018 4:00 PM3/12/2018 5:00 PM Supplier ABETHLEHEMPA297Plant ESulligentALCarrier B3/12/2018 4:00 PM3/12/2018 5:00 PM Supplier EChicagoIL240Plant DGreenvilleNCCarrier B3/12/2018 4:00 PM3/12/2018 5:00 PM Supplier FPigeonMI2845Plant BBEREAKYCarrier A3/12/2018 4:00 PM3/12/2018 5:00 PM Supplier GFrankfortKY10478Plant CBUCHANANMICarrier C3/12/2018 4:00 PM3/12/2018 5:00 PM Supplier DBreaCA2140Plant CBUCHANANMICarrier C3/12/2018 4:00 PM3/12/2018 5:00 PM Supplier FFrankfortKY1210Plant CBUCHANANMICarrier C3/12/2018 4:00 PM3/12/2018 5:00 PM Supplier DBreaCA3860Plant CBUCHANANMICarrier C3/12/2018 4:00 PM3/12/2018 5:00 PM Supplier BTorontoON113Plant BBEREAKYCarrier B3/12/2018 4:00 PM3/12/2018 5:00 PM Supplier EChicagoIL886Plant BBEREAKYCarrier B3/12/2018 4:00 PM3/12/2018 5:00 PM Question 4 Question 4 Using the data in the "Data" table, add a pivot table and pivot chart to show total weight per destination. Question 5 Question 5 On the chart below, put the On Time Percentage on the secondary axis. Then make it a line with markers. DestinationOn time PercentageCount of Shipments Plant A85%3 Plant B99%7 Plant C95%7 Plant D78%3 Plant E50%2 On time PercentagePlant APlant BPlant CPlant DPlant E0.850.990.950.780.5Count of ShipmentsPlant APlant BPlant CPlant DPlant E37732 Data Destination NameDestination CityDestination StateOrigin NameOrigin CityOrigin StateWeightPiecesCarrierScheduled ArrivalETA Plant ALaredoTXSupplier ABETHLEHEMPA1187Carrier A3/9/2018 4:00 PM3/12/2018 5:00 PM Plant BBEREAKYSupplier BTorontoON200Carrier B3/8/2018 4:00 PM3/12/2018 5:00 PM Plant CBUCHANANMISupplier CLaredoTX345Carrier C3/9/2018 4:00 PM3/12/2018 5:00 PM Plant CBUCHANANMISupplier DBreaCA5900Carrier C3/12/2018 4:00 PM3/12/2018 5:00 PM Plant BBEREAKYSupplier EChicagoIL2406Carrier B3/12/2018 4:00 PM3/12/2018 5:00 PM Plant ALaredoTXSupplier FPigeonMI12346Carrier A3/9/2018 4:00 PM3/12/2018 5:00 PM Plant BBEREAKYSupplier BTorontoON1400Carrier B3/12/2018 4:00 PM3/12/2018 5:00 PM Plant DGreenvilleNCSupplier BTorontoON7523Carrier B3/12/2018 4:00 PM3/12/2018 5:00 PM Plant ALaredoTXSupplier EChicagoIL134Carrier A3/9/2018 4:00 PM3/12/2018 5:00 PM Plant DGreenvilleNCSupplier GFrankfortKY1760Carrier B3/8/2018 4:00 PM3/12/2018 5:00 PM Plant BBEREAKYSupplier BTorontoON3720Carrier B3/12/2018 4:00 PM3/12/2018 5:00 PM Plant CBUCHANANMISupplier DBreaCA230Carrier C3/12/2018 4:00 PM3/12/2018 5:00 PM Plant ESulligentALSupplier ABETHLEHEMPA297Carrier B3/12/2018 4:00 PM3/12/2018 5:00 PM Plant DGreenvilleNCSupplier EChicagoIL240Carrier B3/12/2018 4:00 PM3/12/2018 5:00 PM Plant BBEREAKYSupplier FPigeonMI2845Carrier A3/12/2018 4:00 PM3/12/2018 5:00 PM Plant CBUCHANANMISupplier GFrankfortKY10478Carrier C3/12/2018 4:00 PM3/12/2018 5:00 PM Plant CBUCHANANMISupplier DBreaCA2140Carrier C3/12/2018 4:00 PM3/12/2018 5:00 PM Plant CBUCHANANMISupplier FFrankfortKY1210Carrier C3/12/2018 4:00 PM3/12/2018 5:00 PM Plant CBUCHANANMISupplier DBreaCA3860Carrier C3/12/2018 4:00 PM3/12/2018 5:00 PM Plant BBEREAKYSupplier BTorontoON113Carrier B3/12/2018 4:00 PM3/12/2018 5:00 PM Plant BBEREAKYSupplier EChicagoIL886Carrier B3/12/2018 4:00 PM3/12/2018 5:00 PM