Counting Things.

• This is chapter 13.
  • Some of this might be basic, but some will be more advanced.
  • Good chapter, read it.
  • Grab this dataset.
    • I grabbed it from kaggle
    • I removed all but USA-PA events.
    • It is still big, 2367 records.
  • Save it as an excel file, or you will lose everything.
  • Highlight the range and define names for each of the columns
    • Formulas tab, Defined Names, Create from section.
  • Count, counta, countblank
    • All take a range and count the values.
    • Nothing in this dataset to simply count, so
      • Add a worksheet called sandbox
      • add the following
        • I used =3/0 for A5
        • And =asdf() for A6
        • =3+2 for A7
        • A9 and A10 are blank.
        • I named the mess junk.
      • use count, counta, countblank on junk
        • 
    • Cell contents identification
      • There are a huge number of is* functions.
      • Take a cell and return true if the cell matches the query
      • These are in appendix A, page 1041.
      • Take a moment to look at these.
    • You can use these to count with an array function
      • =sum(if(isXXXX(range),1,0))
      • =sum(if(iserror(junk),1,0)) as an array formula
      • 
      • Apparently errors just store strings so you can just countif them
      • =countif(junk,"#NAME?")
    • Countif
      • As we have seen, countif will count the literal string
      • =countif(shape,"triangle")
      • Note here that excel is not case sensitive.
      • Countif will also count contents of a cell
      • 
      • You can give it a formula in quotes
      • =countif(duration__seconds, "<10")
      • operators = {=, <>, <, <=, >, >=}
      • You can also use a cell content, but need string concatenation
      • = countif (durations__seconds,"<"&k17)
      • 
      • 
      • Notice the wild cards in strings as well.
      • Look at page 304 for a nice set of countif examples.
    • More advanced
      • We can count using several countifs
        • I know this example is dumb, but it is the best I can think of right now.
        • 
        • 
      • But we can also use countifs
        • Takes multiple ranges and criteria
        • 
        • We could use multiple ranges.
        • 
        • Notice, this is an and.
        • Can you do this with an array formula
        • 
        • How would you do edinboro or light?
        • Did you get 474 or 476. If you got 476, see a math professor.