Counting

• We will be working starting this worksheet.
  • It is some form of the computer literacy study.
  • Please grab it.
• I renamed a few fields
  • Go to the name manager
    • Actual is act
    • Capacity is Cap
    • Others are mostly the same.
  • There is a table of names
    • Name Manager -> Use In Formula -> Paste Names
    • This was in the notes last time but we didn't get to it.
• Our goal for the day is to understand counting.
  • The basics are easy.
    • count - count the numeric values in a range.
    • counta - count all non-blank values in a range.
    • cuontblank - count blanks.
  • But there are two more.
    • countif - a conditional count we have just barely scratched.
    • countifs - what?
• Let's start by getting a new worksheet, "Subject Enrollment"
  • Get a list of unique subject names.
  • Count the number of times a class in each subject was offered.
• Countif has a strange (in my opinion) test condition
  • It can be a constant value "Acct" or 30 and it will look for exact matches
    • This is essentially an equal command.
  • It can be a text pattern
    • I'm having trouble finding a real reference, but
      • * matches 0 or more characters
      • ? matches exactly one character.
    • This can be directly in the computation
    • Or in a cell listed as the argument.
    • Under where you have the counts. by subject.
    • =countif(subject,"A*")
    • In a cell (say a20) put ???, then =countif(subject, a20);
    • This does not appear to be full regex, just two different matches.
    • Try several. *U*, *U?, *SC*,
  • It can be a simple comparison expression
    • Numeric comparisons are inside of quotes
    • Let's search for the classes with less than 20 actual students.
    • =countif(actual,"<20")
    • Strange but true.
    • <, <=, > >=, <>, =
    • You can get a little more range with a strange trick.
      • In a cell, put a value, 20, say a23
      • =countif(Actual, a23) will count the classes with .
      • =countif(actual, "<="&a23)
      • =countif(actual, ">="&a23)
      • & is string concatenation.
      • Try putting =">=23" in a cell and reference this cell.
    • I don't think you can do logical operations in the boolean test.
    • But you can search for exact results of functions (=today() for example)
  • There is another method for doing this, but can you build a frequency distribution of courses with 0-4, 5-9, 10-14... students enrolled with countif?
    • You will probably need a subtraction in most fields.
• There are equivalent sumif and averageif functions.
  • Can you find the total, average students in each topic
  • 
• There are *ifs functions as well.
  • countifs(range1, criteria1, range2, criteria2, ...)
  • Can you build this table
  • 
  • How about a table of student enrollment by subject by term.
  • How about the average enrollment by subject by term?