I have a list of Data where people received points, example table below
Date | Person | Points |
---|---|---|
1/1/16 | Jackie | 1 |
2/1/16 | Rob | 1 |
2/15/16 | Jackie | 3 |
3/7/16 | Frank | 5 |
6/16/16 | Frank | 1 |
6/16/16 | Frank | 1 |
Let's just pretend those were all the possible dates where people could receive points. I want to create a table like the one shown below:
11/7/16 | Jackie | Frank |
---|---|---|
Daily | 0 | 0 |
MTD | 0 | 0 |
YTD | .2 | 1.4 |
You can see that Rob is not present (I know how to filter so that's not the problem), but also, the YTD is an average of total number of points achieved by that individual divided by the number of unique days where they could have gained points (Frank = 7 pts/5 days since 6/16 has two entries). I can total up the points gained by each person, and I can determine how many days people could have received points, however, when I make enter the calculations as:
SUM([Score])/COUNTD([Days])
It only counts the distinct days where the individual received points instead of all possible days. So I end up with something like this.
11/7/16 | Jackie | Frank |
---|---|---|
Daily | 0 | 0 |
MTD | 0 | 0 |
YTD | 1 | 3.5 |
Where Frank is at 3.5 (7 pts/2 days) and Jackie is at 1 (1 pt/1 day). Obviously this inflates their average score dramatically.
Could someone explain what I need to do? I'd really appreciate it.
Thank you!
Hi Peter,
Do you have an example workbook you could attach?
Walt