5 Replies Latest reply on Dec 17, 2014 12:09 PM by luciana.suran

# Calculate WINDOW_AVG on table for only those IDs with no missing values?

Hi,

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.

I've tried filtering out companies with missing values but when I calculate a WINDOW_AVG the data for the companies with missing data is still used in the calculation even though they are hidden from the view.

Anyone out there with a tip? I'm sure there is a simple solution but I can't solve it...

Thank you!

• ###### 1. Re: Calculate WINDOW_AVG on table for only those IDs with no missing values?

Luciana:

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

• ###### 2. Re: Calculate WINDOW_AVG on table for only those IDs with no missing values?

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.

• ###### 3. Re: Calculate WINDOW_AVG on table for only those IDs with no missing values?

Got it, I was just looking at the image. If you are already looked at this one Joe Oppelt, please go ahead and wrap it up.

Nice work!

..kk

• ###### 4. Re: Calculate WINDOW_AVG on table for only those IDs with no missing values?

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.

See attached.

• ###### 5. Re: Calculate WINDOW_AVG on table for only those IDs with no missing values?

A MILLION thanks Joe.  This is great!!!