CS 105

Week 10, Part 2

MP6: More Excel!

Released: Later Today
Due: Tuesday, April 7 (8:00pm)
On the course website

Activities

Excel Functions

Arithmetic Functions
SUM, AVERAGE, COUNT
SUMIFS, AVERAGEIFS, COUNTIFS
MAX, MIN

Lookup Functions
INDEX, MATCH
VLOOKUP

Conditionals in Excel

// Counts cells in B:B equal to 30
=COUNTIFS(B:B, "30")
// Counts cells in B:B equal to C1
=COUNTIFS(B:B, C1)
// Counts cells in B:B equal to or greater than C1
=COUNTIFS(B:B, ">=" & C1)

Conditionals in Strings

When using conditionals on strings, we can use wildcards to allow us to match partial strings.

* (asterisk) matches zero or more characters

Conditionals in Strings

=COUNTIFS(B:B, "Hello*")
"Hello" // Matches 
"Hello World" // Matches
"Hello CS 105" // Matches
"Welcome and Hello" // Does NOT match
"Hi World" // Does NOT match
"Hi, Hello" // Does NOT match

Conditionals in Strings

=COUNTIFS(B:B, "*Hello*")
"Hello" // Matches 
"Hello World" // Matches
"Hello CS 105" // Matches
"Welcome and Hello" // Matches
"Hi, Hello" // Matches
"Hi World" // Does NOT match

Conditionals in Strings

? (question mark) matches exactly one character

Conditionals in Strings

=COUNTIFS(B:B, "?un")
"sun" // Matches 
"pun" // Matches
"fun" // Matches
"funny" // Does NOT match
"hello" // Does NOT match
"un" // Does NOT match

Conditionals in Strings

"?or*"     // Pattern
"World"    // Cell Value

A) Matches pattern
B) Does NOT match pattern

Conditionals in Strings

"?or*"     // Pattern
"orange"    // Cell Value

A) Matches pattern
B) Does NOT match pattern

Conditionals in Strings

"?or*"     // Pattern
"preempt"    // Cell Value

A) Matches pattern
B) Does NOT match pattern

Conditionals in Strings

"?or*"     // Pattern
"forward"    // Cell Value

A) Matches pattern
B) Does NOT match pattern

Conditionals in Strings

"?or*"     // Pattern
"realtor"    // Cell Value

A) Matches pattern
B) Does NOT match pattern

Random Numbers

Woah, that is so random!

Random Numbers

13

Random Numbers

83

Random Numbers

4, 5, 7, 13, 16, 18, 21, 29, 30, 45, 48, 54, 56, 60, 64, 66, 75, 80, 85, 86, 95, 96

Random Numbers

Odd numbers were chosen twice as often as even numbers -- two odd numbers for every on eeven number.

Numbers ending in 7 were chosen nearly 6x as often as numbers ending in 0.

Prime numbers were chosen 2.5x more often than non-primes.

Humans are extremely bad at picking random numbers!

RAND

Excel provides the RAND function to generate a random number, allowing you to run simulations within Excel.

=RAND()

Excel

Download Sheet #1

Excel, Sheet #2

Download Sheet #2