Transcript CMPT155 - Manhattan College

MEDIAN()
 Median value: the number that falls in the middle.
 1, 2, 5, 7, 10
 If you have an even amount of numbers, Excel averages
the two middle numbers to generate the median.
 1, 2, 4, 7
MAX() and MIN()
 pick the largest or smallest value of a series of cells.
 MAX(range)
 MIN(range)
 ignore any nonnumeric content.
LARGE() and SMALL()
 What if you want to pick the top 3?
 LARGE(range, position)
 SMALL(range, position)
 e.g.,
 LARGE(A2: A12, 2)
 SMALL(A2: A12, 1)
Exercise
 Modify StudentGrades2.xlsx
 home.manhattan.edu/~tina.tian
 Add the following statistics at the bottom of the table
 Total
 Average
 Median
 Highest Score
 Second Highest Score
 Third Highest Score
 Lowest Score
ABS()
 gives you the absolute value of a number.
 = ABS (-3)
 Use IF()?
ROUND()
 rounds a numeric value to whatever level of precision
you choose.
 ROUND(number_to_round, number_of_digits)
 = ROUND(3.987, 2)
 = ROUND(A2, 2)
 rounds 1 through 4 down
 rounds 5 through 9 up
RAND()
 gives you a random fractional number that is less than
1, but greater than or equal to 0.
 0.778526443457
 = RAND()
 Generate a random whole number between 0 and 10?
Generate a random whole number between 0 and 100?
COUNT(), COUNTA() and
COUNTBLANK()
 COUNT() counts the number of cells that have
numeric input.
 =COUNT(range)
 COUNTA() counts cells with any kind of content.
 COUNTBLANK() counts the number of empty cells.
COUNTIF()
 Download CountIfExample.xlsx
 home.manhattan.edu/~tina.tian
 Counts only the cells you specify
 COUNTIF(range, criteria)
 = COUNTIF(C1:C20, “>500”)
Exercise
 Redo the exercise Fruit_Purchases.xlsx
 Download from home.manhattan.edu/~tina.tian
VLOOKUP()
 Finds a specific row in a large table by
 Scanning the values in a single column from top to
bottom
 Retrieving other information from the same row
 Download VLookupExample.xlsx
 What is the name and price of the product with ID of 3?
VLOOKUP()
 VLOOKUP(lookup_value, range,
column_index_number, FALSE)
 lookup_value: what you are seeking
 range: from the column that contains the lookup_value
 column_index_number: position of the column which
contains the data you want to retrieve
 FALSE: exact match
VLOOKUP()
 VLOOKUP(lookup_value, range,
column_index_number, TRUE)
 TRUE: interval_threshold

All numbers starting at the threshold value up to but not
including the next threshold number
 Download StudentGrades4.xlsx
 Homework 2 is due on Friday, Feb 27.
 Test 1 is scheduled on Friday, Mar 6.