CS&E 1111/CSE 1112 Pre Lab 2

Download from Carmen: RoundFun.xlsx, and Sales.xlsx

ALL BEGINNING FILES MUST BE DOWNLOADED FROM Carmen OR THEY WILL NOT BE ACCEPTED.

PART 1: Complete on SIMnet.

Go to Carmen then to Modules then to McGraw Hill Campus then to SIMnetfor your assignment for Prelab 2.

PART 2: FORMATTING AND ROUNDING NUMBERS

Complete the RoundFun.xlsx problem as directed. Be sure to read the information on the Round function in the Excel section of your Course Notes:

  1. Open the RoundFun.xlsx that you downloaded from Carmen.
  2. Enter a starting value: In Cell A1, enter the number 123560.94732 and change the cell format to display 5 decimal places.
  3. Enter Column Titles:
  4. In cell A3 enter the text label “Number”
  5. In cell B3 enter the text label “Decimal Places”
  6. In cell C3 enter the text label “Result”.
  7. Copy the contents of cell A1:In cell A4, enter the formula =A$1 and copy the formula down the column into the range A4:A15.
  8. Enter values in Decimal Place column:In cell B4, enter the number 5. In cell B5, enter the formula =B4-1 and copy the formula down the column into the range B5:B15.
  9. Use the Round Function:In cell C4, enter the formula =ROUND(A4,B4) and copy the formula down the column into the range C4:C15.
  10. Format cells A4:C15 to display 5 decimal places.
  11. Enter a comment in cell C15 (right click on the cell and choose “Insert Comment”) explaining why the result in cell C15 is 0.
  12. Save your completed workbook to RoundFun.xlsx It should be similar to the one seen in Figure 1.

PART 3: USING COUNTIF & SUMIF FUNCTIONS

You have volunteered to lead a group of Girl Scouts and are now involvoled in the annual cookie sale fund raiser. Its time to aggregate the information from your troop’s sales. Use the following directions to create the Sales.xlsx worksheet:

  1. Open the file Sales.xlsx you downloaded from Carmen.
  2. Make sure to read the sections on COUNTIF and SUMIF in Chapter 1.3 of your Course Notes.
  3. Open the file Sales.xlsx from Carmen and save to your personal directory lab 3 folder.
  4. In cell B20, write a formula to determine the Total number of orders for slim mints.
  5. The COUNTIF function will count the number of cells that meet a specified critierion.
  6. The 1st argument of the COUNTIF Function is the range you wish to check against the critierion, in this case the range is B4 through B16.
  7. The 2nd argument is the critierion. In this case, the criterion is ‘Slim Mints’ and is listed in cell A20
  8. What modifications, if any, need to be done so that your formula can be copied down the column to determine the number of orders for So-di-Sos and Tongas? Modify your formula as needed.
  9. In cell C20, enter a formula to total the number of boxes purchases for Slim Mint cookies.
  10. The SUMIF function will selectively add a series of cells if a specific critierion is met.
  11. The 1st argument of the SUMIF Function is the range you wish to check against the criteria, in this case the rage is B4 through B16.
  12. The 2nd argument is the critierion. In this case the criterion is ‘Slim Mints’ and is listed in cell A20
  13. The 3rd argument is the range to be added if the corresponding cells in the first range meet the critierion. In this case the range is C4 through C16.
  14. Your completed workbook should be similar to the one seen in Figure 2.

FILES TO Be submitted TO Carmen

  • Files must be in a zipped file before you submit them to Carmen.Place the files RoundFun.xlsx and Sales.xlsx into a zipped file named Prelab 2 and then submit to Carmen.

Page 1