Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
add comment

2 Answers

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
add comment

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
add comment

Your Answer

 
discard

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.