I have couple questions here;
I would like to calculate a WINDOW_AVG on a table but only for those IDs that have a valid rating for each date in the window. In this case, I want to calculate a WINDOW_AVG that includes only companies AAA, CCC, and EEE since they have a rating for each date. This should be 3.3333.
1- what do you mean by include only AAA, CCC, and EEE. I see that except FFF, there is valid rating for all companies right
2- how did you get the 3.3333 number
Hope this helps.
KK -- If you open the workbook, BBB and DDD have some blank slots in the grid. She wants to average only those that have a rating for all dates displayed.
Create some table calcs that determine how many months you have, how many data points a given company has, see if those two values match.
If they match, then you company has a full set, so add up the ratings. (Else value = 0).
Then add up all the added ratings, and divide by the number of months on the chart times the number of companies that were included.
I broke it into a bunch of separate calcs to show step by step, but some of that can be combined.
I put the respective values in tooltips so you can see how it progressed as I created the steps.
Be sure to change the actual [average] calc to be Table Down.
Book1 B.twbx 13.4 KB
A MILLION thanks Joe. This is great!!!