# How to take the average of values only if the identifier is the same?

1. ## How to take the average of values only if the identifier is the same?

Hello,

I am trying to calculate the average of values that correspond to the same identifier. How could I do that?

I have a forecast for 11 or 12 months for the same firm and then the actual value that was realized for that year.

I would like to compute the average of the forecast values for each unique firm-year (that is the ID) and then subtract the average from the actual (realized) value.

I have uploaded an excel file with the forecasts/actuals and I have also indicated the desired outcome.

Thanks!  Register To Reply

2. ## Re: How to take the average of values only if the identifier is the same?

H2:

=G2-(SUMPRODUCT((\$D\$2:\$D\$181=D2)*(\$F\$2:\$F\$181))/SUMPRODUCT(--(\$D\$2:\$D\$181=D2)))

and copy downwards

This is the SUM of all of F, where D is the same, divided the the number of times D was the same. And then subtracting from G2.

You could also do this with a Pivot Table.  Register To Reply

3. ## Re: How to take the average of values only if the identifier is the same?

See if this is what you want?

I pulled out a list of unique ID's....

 L M 2 A2002 -0.76909 3 A2003 -0.32727 4 A2004 0.965 5 A2005 1.09 6 A2006 1.603846 7 A2007 1.83 8 AA2002 1.379167 9 AA2003 1.160833 10 AA2004 1.836667 11 AA2005 1.8575 12 AA2006 2.7675 13 AAI2001 0.14 14 AAI2002 0.149167 15 AAI2003 0.595 16 AAI2004 0.390833 17 AAI2005 -0.02538 18 AAI2006 0.582

M2=AVERAGEIF(\$D\$2:\$D\$181,L2,\$F\$2:\$F\$181) copied down  Register To Reply

4. ## Re: How to take the average of values only if the identifier is the same?

@FDibbins, yes this is what I wanted.

Could you please explain to me how to do it, because when I copied M2=AVERAGEIF(\$D\$2:\$D\$181,L2,\$F\$2:\$F\$181), it gave me an error?  Register To Reply

5. ## Re: How to take the average of values only if the identifier is the same?

Did you put the unique ID's in L2:L18?  Register To Reply

6. ## Re: How to take the average of values only if the identifier is the same? Originally Posted by FDibbins Did you put the unique ID's in L2:L18?
Yeah I didn't, I should be able now. Can you please tell me how to extract the unique IDs only?  Register To Reply

7. ## Re: How to take the average of values only if the identifier is the same?

I kinda cheated, I copied the entire column to L2, then used Remove Duplicates  Register To Reply

8. ## Re: How to take the average of values only if the identifier is the same? Originally Posted by FDibbins I kinda cheated, I copied the entire column to L2, then used Remove Duplicates
I almost figured it out.

Now the formula is working but not entirely.

I managed to extract the unique IDs in column L, and the formula calculates the average for the first 505 unique firm-years and then there is some error.

I have uploaded my full sample to show you what the problem is.

How do you think this can be resolved?  Register To Reply

9. ## Re: How to take the average of values only if the identifier is the same?

The range you are using only goes down to 5041, but your data goes to 20311, you need to adjust the ranges to suite.

However, because you have such a long list, I have taken another approach in the attached file. I created a table with the OFTIC down 1 column, then the years across the top - then changed the formula to averageifS(). You can then do away with column D.

Take a look and see if this is something you can work with?  Register To Reply