Hi all,
I'm creating metrics tracker for next year and trying to do something fancy with viewership goals - it uses the year's running data to adjust the goals for each day of the week. Here's what's supposed to happen:
1. Each day the user enters raw data on how many views they've gotten in the Total (inc. all stories) section (U60:BK60, U104:BK104, U148:BK148, U192:BK192, U236:BL236 and U280:BK280).
2. The table at T284 calculates the average views for each day of the week in a specified month using the data entered in the previous step. This uses an array formula with nested IF steps: (Don't show #N/A errors > Average > Narrow by month > exclude blank cells > narrow by weekday, use this data)
3. The final column in the table at AG284 takes the result from the previous step and calculates the average percent of views each weekday gets out of the whole on a running basis. (e.g. Average of all Mondays in the year / average of all days in the year)
4. Finally, the goal section in the cells below the data entry point (U61 etc.) takes the output from the previous step and combines it with a few other metrics to determine a goal for the day: (Previous year's views for the month * previous step's data * % increase target for this year / Number of days in this month).
That should be it at this stage. But the sheet is complaining about circular references. It identifies various cells in the goal section as containing them, but the option to hunt them down in the formulas tab is greyed out. Also, when I go to the weight section in step 3 or the goal section in step 4, hitting enter in the formula bar returns a "-" instead of the number it should.
Any ideas on how to fix this? I've had to teach myself how to create this, so I'm open to suggestions from the experts.
Bookmarks