+ Reply to Thread
Results 1 to 9 of 9

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

  1. #1
    Registered User
    Join Date
    04-17-2014
    Location
    Rotterdam, The Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    17

    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!
    Attached Files Attached Files

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,468

    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.
    Attached Files Attached Files
    Last edited by daffodil11; 05-23-2014 at 06:06 PM.
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,437

    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
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    04-17-2014
    Location
    Rotterdam, The Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    17

    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?

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,437

    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?

  6. #6
    Registered User
    Join Date
    04-17-2014
    Location
    Rotterdam, The Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    17

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

    Quote Originally Posted by FDibbins View Post
    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?

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,437

    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

  8. #8
    Registered User
    Join Date
    04-17-2014
    Location
    Rotterdam, The Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    17

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

    Quote Originally Posted by FDibbins View Post
    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?
    Attached Files Attached Files

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,437

    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?
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 12-04-2013, 09:45 PM
  2. [SOLVED] add increment identifier to unique values
    By hluk in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 02-19-2013, 04:44 PM
  3. Column A Identifier, Coumn B Values, Perform Mathematical Function
    By anon in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-22-2012, 08:50 PM
  4. Find minimum for all values with same identifier within an array
    By dhs in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-13-2011, 10:48 PM
  5. Replies: 3
    Last Post: 07-07-2011, 08:24 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1