More Counting

• Counting the most frequently occurring number
  • Use mode
  • =countif(duration__seconds,mode(duration__seconds))
• Counting the most frequently occurring text string
  • Array formula
  • =max(countif(city,city))
• 
• An exact string match
  • Exact function returns true if the strings match exactly, including case.
  • Again an array function
  • =sum(if(exact("City Name",city),1,0))
  • 
• We saw the count uniques last time
  • =sum(1/countif(city,city)) as an array
  • Unfortunately, this will fail if there are blanks.
  • =sum(if(countif(shape,shape)=0,"",1/countif(shape,shape)))
  • 
• He does not document two functions I use sometimes.
  • averageif, averigeifs
  • sumif, sumifs
  • How would you look these up?
    • Help in the formula tab
    • On line google search
    • Look in the book, but that is not helpful here.
  • Let's build a table of Encounters by hour by a given city.
    • First let's build an hour column.
      • Next to the longitued column, insert a new column if needed.
      • Use the function =hour(a2) in cell j2
      • Copy this down
      • 
      • 
      • Name this range. (hour will work)
    • Start a new worksheet
      • Build a place to enter data.
      • In Cell A1 put the label "City"
      • In cell B1 put the data Erie
      • Color B1 light blue to show it is a data entry cell
      • In A3 put "Hour", and B3 put "Frequency"
      • 
    • Extract the hours from the hour data set.
      • I want to use the advanced filter function on the data tab, but it will no use data without a header.
      • So I need to enter the range manually, note it ends at J2367
      • What's more, it will only copy to the sheet that the data is stored on.
      • So go back to UFO-PA
      • Select Advanced from the Sort & Filter workgroup of the Data tab.
      • Select space for the result (probably 25 cells)
      • 
      • Sort the times, copy them to the new work area a4 down
      • Looks like UFOs appear round the clock.
      • 
    • Use a countifs function to compute the frequency for the given city for each hour.
      • 
    • Add an Average Duration column in column C
    • Use the averageifs function to compute the average duration
      • Rats, when there are no citings, averageif does not work well.
      • Use an if to wrap this.
      • 
      • 
    • Add another column and compute the total duration for the city for each hour.
    • Add a check column where you test to see if the averageifs function worked.
      • 
      •