Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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



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

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.