Average rating by name.

[prattmic]

I am creating a spreadsheet for one of my favorite shows that includes
director name and rating for each episode. What I want to do is show the
average episode score for each director. In a sense it needs to find each
row with a certain directors name in the director column, then grab each
episode rating from the director's episodes and average them.

I hope this makes sense.

Thanks

 

[Barb Reinhardt]

=AVERAGE(IF(A1:A10="DirectorName,B1:B10))

Commit with CTRL SHIFT ENTER

A1:A10 contain the director, B1:B10 contain the ratings for each director.

I believe there is a more elegant way in Excel 2007.

 

[Jim Thomlinson]

You can use an array formula something like this...

=AVERAGE(IF(A2:A100="DirectorName", B2:B100))

Note that it is an array formula so it must be commited with
Ctrl+Shift+Enter and not just a regular Enter.

 

[prattmic]

Thank you, that worked perfectly. I am using Excel 2007, what do you mean by
"more elegant"

 

[prattmic]

One more quick question, is it possible to show how many episodes that person
directed. (Basically, counting how many ratings were averaged.)

Thanks for all the help!

 

[prattmic]

Nevermind, I found the COUNTIF function.

One more quick question, is it possible to show how many episodes that person
directed. (Basically, counting how many ratings were averaged.)

Thanks for all the help!

 

[Jim Thomlinson]

=countif(A2:A100, "=DirectorName")
or
=Countif((A2:A100, C1)
Where c1 holds the directors name. This is not an array formula so you can
enter it as usual.

 

[prattmic]

Thanks for the help. I have been doing the same thing for Writers, however
alot of episodes have multiple writers, and I was wondering if there was a
way to make this only look to see if the person's name is contained in the
cell, that way I would not have to create a separate entry for each group of
writers.

Thanks