I am looking for a way to divide groups of 16 annual $ values in a column by a total row that repeats every 16. I created the "Total for Calc" column in a separate column using formula IF(MOD(ROW(),16),"",SUM(OFFSET(J16,,,-16))) in the original spreadsheet. I need to proceed down 16 rows and divide the groups of 16 using the next total row, repeat, repeat, etc.
My goal is to create a % composition for each media within the group of 16 (should total 100%). I have 43,456 rows and multiple years of data to trend, so it's not possible to do manually. If I can get the formula and pattern down, I can modify across the years and columns. Thank you in advance for the help! - Melody
I've listed an example, with manual calculations for year 2020 to demonstrate what I need to accomplish on a larger scale (there are market names in the excluded column).
My goal is to create a % composition for each media within the group of 16 (should total 100%). I have 43,456 rows and multiple years of data to trend, so it's not possible to do manually. If I can get the formula and pattern down, I can modify across the years and columns. Thank you in advance for the help! - Melody
I've listed an example, with manual calculations for year 2020 to demonstrate what I need to accomplish on a larger scale (there are market names in the excluded column).
| Media | 2020 $ (000) | Total for Calc | % of media expenditure per category by DMA 2020 | 2021 $ (000) | Total for Calc | % of media expenditure per category by DMA 2021 |
| Direct Mail | $587 | 27% | $666 | = E1/every 16th row in column E (progressively)…to look like the %s in the example to the left | ||
| Mobile | $465 | 21% | $572 | = E2/every 16th row in column E (progressively) | ||
| TV OTA | $97 | 4% | $105 | |||
| PC or Laptop | $213 | 10% | $265 | |||
| Directories | $166 | 8% | $178 | |||
| OOH | $121 | 6% | $147 | |||
| TV Digital | $21 | 1% | $33 | |||
| Cable TV | $103 | 5% | $97 | |||
| Radio OTA | $107 | 5% | $116 | |||
| OTT | $21 | 1% | $28 | |||
| News Print | $123 | 6% | $134 | |||
| News Digital | $88 | 4% | $102 | |||
$34 | 2% | $41 | ||||
| Mags Digital | $19 | 1% | $23 | |||
| Radio Digital | $17 | 1% | $20 | |||
| Mags Print | $14 | $2,195 | 1% | $14 | $2,542 | |
| Direct Mail | $112 | 28% | $127 | |||
| Mobile | $96 | 24% | $118 | |||
| TV OTA | $27 | 7% | $29 | |||
| PC or Laptop | $37 | 9% | $46 | |||
| Directories | $32 | 8% | $35 | |||
| OOH | $21 | 5% | $25 | |||
| TV Digital | $2 | 1% | $4 | |||
| Cable TV | $7 | 2% | $7 | |||
| Radio OTA | $20 | 5% | $22 | |||
| OTT | $4 | 1% | $5 | |||
| News Print | $19 | 5% | $21 | |||
| News Digital | $13 | 3% | $15 | |||
$6 | 2% | $8 | ||||
| Mags Digital | $4 | 1% | $5 | |||
| Radio Digital | $2 | 0% | $2 | |||
| Mags Print | $3 | $404 | 1% | $3 | $470 | |
| Direct Mail | $56 | 27% | $63 | |||
| Mobile | $44 | 22% | $54 | |||
| TV OTA | $11 | 5% | $13 | |||
| PC or Laptop | $20 | 10% | $25 | |||
| Directories | $15 | 7% | $16 | |||
| OOH | $11 | 6% | $14 | |||
| TV Digital | $3 | 1% | $4 | |||
| Cable TV | $6 | 3% | $7 | |||
| Radio OTA | $10 | 5% | $11 | |||
| OTT | $2 | 1% | $3 | |||
| News Print | $12 | 6% | $13 | |||
| News Digital | $8 | 4% | $10 | |||
$3 | 2% | $4 | ||||
| Mags Digital | $0 | 0% | $0 | |||
| Radio Digital | $2 | 1% | $2 | |||
| Mags Print | $0 | $204 | 0% | $0 | $238 |
