Hi there,
I am looking for a formula, that calculates the weekly coverage extension.
In the example file there are 6 accounts in total and a timeframe of 5 weeks. The formula I would like to use has to calculate, how many of the 6 accounts at least scored in one week so far.
Of the 6 accounts in total, 5 scored in week 24. The coverage for week 24 is 3/6 = 50%. For week 25, there are 4 accounts that scored, whereof 2 are first time scores. Only looking at week 25, it were 4/6 = 66%. But in the entire date frame of week 24 - 28, the coverage at week 25 already is at 5/6 = 83%. So the value I need is 83% for week 25. Only account 6 did not yet score (and thus does not show up in the pivot at all).
I thought about calculating for week 25 COUNTIFS week 25 > 0 AND week 24 not > 0, then divided by the total amount of distinct accounts (and for week 26 then week 24 & week 25 not > 0)
But I cannot even calculate the distinct number of accounts in tab "Domains"... (maybe because it is a table? It has to be, because this is meant to be re-fill weekly without having to adjust the formula range)
TABLE WITH SCORES (=MQL)
TABLE WITH ACCOUNTS:
As you can imagine, in real life this file contains 1000+ Accounts and 1000+ scores per week...
Happy if anyone of you knows the magic formula
I am looking for a formula, that calculates the weekly coverage extension.
In the example file there are 6 accounts in total and a timeframe of 5 weeks. The formula I would like to use has to calculate, how many of the 6 accounts at least scored in one week so far.
Of the 6 accounts in total, 5 scored in week 24. The coverage for week 24 is 3/6 = 50%. For week 25, there are 4 accounts that scored, whereof 2 are first time scores. Only looking at week 25, it were 4/6 = 66%. But in the entire date frame of week 24 - 28, the coverage at week 25 already is at 5/6 = 83%. So the value I need is 83% for week 25. Only account 6 did not yet score (and thus does not show up in the pivot at all).
I thought about calculating for week 25 COUNTIFS week 25 > 0 AND week 24 not > 0, then divided by the total amount of distinct accounts (and for week 26 then week 24 & week 25 not > 0)
But I cannot even calculate the distinct number of accounts in tab "Domains"... (maybe because it is a table? It has to be, because this is meant to be re-fill weekly without having to adjust the formula range)
TABLE WITH SCORES (=MQL)
TABLE WITH ACCOUNTS:
As you can imagine, in real life this file contains 1000+ Accounts and 1000+ scores per week...
Happy if anyone of you knows the magic formula
