The If Statement

• For this session we will be using The Marijuana Related Crime dataset from the Denver Open data Catalog
  • The data has the following description
    • INCIDENT_ID. Unique identifier of the General Offense (GO) police report which documents the reported crime.
    • FIRST_OCCURENCE_DATE. The first possible date/time when the crime could have occurred.
    • LAST_OCCURENCE_DATA. The last possible date/time when the crime could have occurred.
    • ReportDate. The date when the General Offense report was taken.
    • Address. The location of the reported crime.
    • DISTRICT_ID. The police district where the reported crime occurred.
    • PRECINCT_ID. The police precinct where the reported crime occurred.
    • OFFENSE_CODE. The Uniform Crime Reporting (UCR) code.
    • OFENSE_TYPE_ID. Textual description of the UCR code.
    • OFFENSE_CATEGORY_ID. Aggregate category used for grouping similar crimes.
    • MJ_RELATION_TYPE. The type of relation to marijuana the reported crime after review.
      • Industry-related crimes involve marijuana and licensed marijuana facilities. These reported crimes are committed against the licensed industry or by the industry itself.
      • Non-Industry crimes are crimes reported where marijuana is the primary target in the commission of these crime but the marijuana has no readily apparent tie to a licensed operation.
  • Download the file, save as an excel worksheet.
  • Just to make life a bit easier delete
    • INCEDENT_ID
    • GEO_X
    • GEO_Y
• The IF statement
  • =if(logical condition, then clause, else clause)

  • evaluate the boolean condition
    If the condition is true execute the then clause
    If the condition is not true execute the else clause
            

  • The values we put in the clauses can be
    • Constants
    • Other expressions
• A simple example
  • Let's name K1:K1372 "Neighborhood"
  • Make a new worksheet
  • Extract the unique values in neighborhood.
  • Count the number of times each neighborhood had a reported incident.
  • Find the integer average of incidents per neighborhood =round(average(:))
  • Name this cell NeighborAvg
  • Add a column to the new table that prints a "Yes" if the negihborhood has a lower than average number of reports.
  • 
  • 
  • If we want, we can put a blank in a clause.
  • Add another column that just prints a yes if the value is high
  • 
  • 
  • For the above average neighborhoods, compute the number of standard deviations above the average that neighborhood is.
    • Compute the standard deviation of the entire set.
    • Call that NeighborSTD
    • If the answer in column D is Yes, (b?-NeighborAvg)/NeighborSTD
    • 
    • 
  • We could even do a nested if.
    • Add another column called Class
    • if the it is above average, put above
    • If it is the average, put average
    • If it is below the average, put below.
    • You will have to if, one inside one clause of the other.
    • 
    • 
    • Excel has a nested if function however
      • Documented here
      • =if(test1, result1, test2, result2, ... testn, resultn)
      • There is no default case, but use TRUE as the last test for default
      • 
  • Some tasks
    • Verify that the last two columns produce the same answer.
    • In the raw data, add a column after Reportdate.
      • If the report data is not the First Occurrence date, report the difference between the two.
      • otherwise report a blank.
      • You will need to change the format of the output to be numeric.
      • You can subtract two dates and get a reasonable result.
      • 
      • 
    • If the Last Occurrence Date is not blank, compute the number of days where the crime could have occurred.
      • you will need a not
      • Isblank returns true if the cell is blank
      • 
      • 
    • Add a second column and only report the range if it is positive
      • I used and, not, isblank in this one
      • 
    • Assume if the Address has a / in it the incident was on the corner of the two streets.
      • I don't know if this is true, but we can work with it.
      • Check to see if the address contains a /, if so, report Intersection
      • We will look at string functions but
      • find(string to find, string to find in)
      • also iserror
      • 
      • 
  • A quick switch
    • There is a switch statement as well.
    • Look at syntax.
    • Find a way to genreate a random number between 1 and 4.
    • Use switch to generate Hearts, Diamonds, Spades, Clubs
  • If time, can we check to see if Offence code, Offense Type ID or Offense Category ID correlate?