Penn State University Computer Science Worksheet

Assignment9:

1. Requirements:
   1. Scorecardspreadsheet
   Formulas: Complete the missing information using formulas
   1. Use COUNTIF in cell D21 to count the number of Rank 2 there is for Year 1
   2. Use COUNTIF in cell F21 to count the number of Rank 1 there is for Year 2
   3. Use COUNTIF in cell H21 to count the number of Rank 4 there is for Year 3
   4. For D22, F22, H22 show the lowest rank for that column D, F, or H where rank 1 is
      the highest rank. Pay attention to this case where the higher the number, the
      lower the rank
   5. For J8:J20, each corresponding row, please get the max values in columns E, G,
      Page 1 of 3

2.

and I. For J8:J20, please set the number format to display 2 decimal places.

1. Use AVERAGE in K11 to find average revenue per employee in columns E, G, and I
2. in F23, find the median of the ranks in column F
3. In J21, use COUNTBLANK on range D6:I20

Assignment 10:

1. Requirements:
   S&P 500 worksheet
   This spreadsheet shows prices and volumes for S&P 500 for a year. Complete the missing
   information (yellow shaded cells) with the appropriate formulas or functions.
   1. a) Except where noted otherwise, format all numbers with zero decimal places
   2. b) Mean
   3. c) Median
   4. d) Standard Deviation P

1. e) Maximum
2. f) Minimum
3. g) Range

1. h) COUNTIF > Mean reference the cell from the Mean calculation to your conditional
   criteria to answer how many are above average
2. i) Create Line Chart for S&P 500 Close on this data set for the year 2020. Set title of
   chart to “S&P 500 2020 Close”. Select style 5. The vertical values on the chart
   should be the S&P Close values and the horizontal values should be the 2020
   dates.