Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a table of data (5000+ rows) with two columns, ID and value.

It may look like this:

ID Value
01 02
01 03
01 17
02 05
03 06
03 28

What I want to do is find the average value for each ID. i.e. =AVERAGE(rows where id=1)

How can I go about this?

share|improve this question
    
You have answers here, but if your result seem to not come out, make sure your values with leading zeroes are not considered text. – datatoo Jul 23 '12 at 14:35

In Excel 2007 and beyond use AVERAGEIF

Example:

=AVERAGEIF(A2:A7,1,B2:B7)

Will return the average of the values in B2:B7 where the corresponding value in A2:A7 is 1.

share|improve this answer
    
That works great for average. Figured it out for 'maxif' =MAX(IF($B$2:$B$4988=G1,$C$2:$C$4988)) – gdogg Jul 20 '12 at 22:32

And, if you need the averages for all the ID values (not just "01"), use a pivot table and summarize by the "average":

enter image description here

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .