Spreadsheet: 199602d1355256553-formula-in-cell-to-result-in-a-1-5-score-via-vlookup-media-trackingv6_reply
Sheet: Media Summary Sheet

Region/Store | Date Start | Date End | Type of Event | Avg Daily Sales | Comparison | Media Spend | Total Sales | Preceding | Gross Profit | Medium | TV Supplier | Radio Supplier | Reach (%) | 3+ Frequency (%) | TARPS | Spots | Result (1-5) | 1. No Effect 2. Slight Effect 3. Reasonable Effect 4. Good Effect 5. Excellent Effect
Melbourne | 2012-02-11 00:00:00 | 2012-02-15 00:00:00 | Sale | 5555 |  | 10000 | 17000 | 4000 |  | TV | Network Ten
Melbourne | 2012-12-20 00:00:00 | 2012-12-22 00:00:00 | Sale | 7777 |  | 12000 | 13000 | 3000 |  | Radio |  | Gold FM
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Radio
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | ACTRadio | AdelaideRadio | BrisbaneRadio | Gold CoastRadio | MelbourneRadio | NewcastleRadio | PerthRadio | SydneyRadio
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | ATN | ATN | ATN | ATN | ATN | ATN | ATN | ATN
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 104.7 | Mix | Triple M | Gold FM | FOX FM | KOFM | 92.9 | 2Day
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Mix | Nova | 97.3 | Hot Tomato | Gold FM | NXFM | Mix | Nova
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | SAFM | Beat105 | SeaFM | Mix |  | Nova | Mix
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Nova |  | Nova |  | 96FM | WSFM
 | First day of media activity | Monday of / following  last day of media activity |  | For period between start and end date | [NAME] score based on avg daily sales compared to the average of all other avg daily sales for that region and sale or campaign | Cost of media | Total  sales for period between start and end date | Total sales for same period/days immediately preceeding start date. | Assigns a 1-5 score based on profit of start to end date as compared to profit preceding. Higher profit during media = higher score. |  |  |  |  |  |  |  | 1-5 score based on feedback from franchisees on success of media |  | TV
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | ACTTV | AdelaideTV | BrisbaneTV | Gold CoastTV | MelbourneTV | NewcastleTV | PerthTV | SydneyTV
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Prime7 | Network Ten | Nine Network | NBN | Network Ten | NBN | Network Ten | Network Ten
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | SCA | Seven Network | Seven Network | Prime7 | Nine Network | Prime7 | Seven Network | Nine Network
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | WIN | WIN | Network Ten | SCA | Seven Network | SCA | WIN | Seven Network
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Legend | Legend 1 | Legend 2 | Legend 3 | Legend 4 | Legend 5
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | ACT | Sale | 1 | TV | 0-50% | 0-40%
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Adelaide | Campaign | 2 | Radio | 50-60% | 40-50%
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Brisbane |  | 3 |  | 60-70% | 50-60%
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Gold Coast |  | 4
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Melbourne |  | 5
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Newcastle
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Perth
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Sydney