Spreadsheet: 193471d1352838575-multiple-formulas-needed-in-one-cell-to-create-a-value-in-a-new-cell-media-tracking-percentage-comparison
Sheet: Media Summary Sheet

Region/Store | Type of Event | Date(s) | Avg Daily Sales | Comparison | Medium | TV Supplier | Radio Supplier | Spend | Reach (%) | 3+ Frequency (%) | TARPS | Spots | Result (1-5) | Feedback
Melbourne | Sale |  | 6335 |  | TV | Network Ten
Newcastle | Sale |  | 3674 |  | Radio |  | ATN
Gold Coast | Sale |  | 3765 |  | TV | Prime7
Brisbane | Campaign |  | 4342 |  | Radio |  | 97.3
Gold Coast | Campaign |  | 3473 |  | Radio |  | SeaFM
Perth | Sale |  | 2895 |  | TV | WIN |  |  |  |  |  |  |  |  |  | Legend | Legend 1 | Legend 2 | Legend 3 | Legend 4 | Legend 5 | Legend 6 |  | Radio
Gold Coast | Sale |  | 5543 |  | Radio |  | ATN |  |  |  |  |  |  |  |  | ACT | 1 | TV | NBN | 97.3 | 0-50% | 0-40% |  | ACTRadio | AdelaideRadio | BrisbaneRadio | Gold CoastRadio | MelbourneRadio | NewcastleRadio | PerthRadio | SydneyRadio
Sydney | Campaign |  | 6431 |  | TV | Seven Network |  |  |  |  |  |  |  |  |  | Adelaide | 2 | Radio | Network Ten | 92.9 | 50-60% | 40-50% |  | ATN | ATN | ATN | ATN | ATN | ATN | ATN | ATN
Gold Coast | Sale |  | 4635 |  | Radio |  | Gold FM |  |  |  |  |  |  |  |  | Brisbane | 3 |  | Nine Network | 104.7 | 60-70% | 50-60% |  | 104.7 | Mix | Triple M | Gold FM | FOX FM | KOFM | 92.9 | 2Day
 |  | So because the value in D10 relates to the "Gold Coast" region (A10) and is a Sale (B10), the formula in E10 would be the average of all values in column D that have Gold Coast as their region and are a sale, as compared to the value in D10. This is then convered to a % and the % outcome convered to a 0 to 5 rating as outlined in the sales tracking Word document. So E10 would add the average of D4 and D8 (4,654), compared to D10 (4,635). This shows  a .40 decrease for D10 as compared to other Gold Coast Sales. This translates to a 2.5 rating based on the tables in the sales tracking formula. Hence, 2.5 is added to the average of Cells  C11 and H33 in the "Tables - Avg Daily Sales" tab as it is radio and Gold FM. |  |  |  |  |  |  |  |  |  |  |  |  |  | Gold Coast |  |  | Seven Network | ATN |  |  |  |  |  | Nova |  | Nova |  | 96FM | WSFM
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Melbourne |  |  | WIN | Beat 105
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Newcastle |  |  |  | Fox FM |  |  |  | TV
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Perth |  |  |  | Gold FM |  |  |  | ACTTV | AdelaideTV | BrisbaneTV | Gold CoastTV | MelbourneTV | NewcastleTV | PerthTV | SydneyTV
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Sydney |  |  |  | Hot Tomato |  |  |  | Prime7 | Network Ten | Nine Network | NBN | Network Ten | NBN | Network Ten | Network Ten
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | KOFM |  |  |  | SCA | Seven Network | Seven Network | Prime7 | Nine Network | Prime7 | Seven Network | Nine Network
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Mix |  |  |  | WIN | WIN | Network Ten | SCA | Seven Network | SCA | WIN | Seven Network
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Nova
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | NXFM
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | SAFM
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Sea FM
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Triple M
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | WSFM