Still looking for the Cheap Seats

• Please start with the 2104 NFL Division ticket price worksheet.
• It should look something like this
  • 
  • We really need Home, Away, Date, Conference, Region and TP for today
  • If you need help getting there ASK NOW.
• I would like to split off some date information.
• We could do this using left/center/right
• But I would like to introduce you to some date functions
  • Grab a new worksheet
  • In cell A1 put =today()
  • Compute the day, month, and year of this date using
    • =day(a1)
    • =month(a1)
    • =year(a1)
  • We can also use weekday(), to find the day of the week.
  • There are functions for extracting hour, min, sec of time.
  • Put 1,2, ... 12 in column F.
  • Copy this to column G.
  • We will format these a bit.
    • Highlight the range in column G, right click and select format cells.
    • Select the Custom choice.
    • In the Type: input box, put ddd
    • 
    • Click OK
    • Go back and put dddd
    • You can look at the preview if you wish.
    • Try mmm
    • That didn't work, why not?
    • In column H try =date(1,f4,1)
    • Now try to format this, custom format (m, mm, mmm, mmmm)
• Back to the NFL
  • Surprisingly, even though column G is not a date value, excel can extract information from it.
  • Add a column between G and H and compute the weekday as a day
  • Add another column and find the month.
    • This is a bit more tricky as it does not properly compute the month.
    • Experiment, you will get it.
    • 
• A Pivot Table allows you to explore relationships between the data
  • The data should be contiguous, or no blank rows or columns.
  • Click in the data and select Pivot Table on the Tables workgroup of the Insert tab.
    • You can take all of the default choices on the popup window.
  • On the new sheet you can use the Pivot Table interface to quickly build tables.
• Drag the Home Team into the Rows box
• Drag the Day into the Columns Box
• Drag the Event into the values box.
  • Note that excel just did the count of event.
  • It doesn't make sense to do anything else.
• Drag the Conference into the Rows box, but put it first.
• Drag the Region into the Rows box, but put it between Conference and Team.
• Note that we can use the +/- boxes to collapse things.
• We can double click on the one Thursday night game and it will give us a table containing that game.
  • Explore this some.
• Remove region and conference from the Rows box
• Remove event from the values box
• Put Avg TP, $ in the values box
  • Notice it decided to sum the values.
  • This is probably not right.
  • Use the drop down menu to select Value Field Settings
  • 
  • Change this to be average.
  • While you are there, format the cells as currency.
• Excel is trying to be helpful, but do the total columns make sense?
  • Since we are looking at averages probably not
  • Using the Grand Totals drop down in the Layout workgroup of the PivotTable Tools Design tab, turn these off.
• You can right click in a column and sort/filter ...
• There is so much more we could do, but for now...
• Explore this a bit more,
  • What region of what conference has the lowest ticket price?
  • Can you discover anything else interesting?