1

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?

1
  • 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, 2012 at 14:35

2 Answers 2

3

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.

1
  • That works great for average. Figured it out for 'maxif' =MAX(IF($B$2:$B$4988=G1,$C$2:$C$4988))
    – gdogg
    Jul 20, 2012 at 22:32
1

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

You must log in to answer this question.

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