Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average rating by name.
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average rating by name.
=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. -- HTH, Barb Reinhardt "prattmic" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average rating by name.
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. -- HTH... Jim Thomlinson "prattmic" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average rating by name.
Thank you, that worked perfectly. I am using Excel 2007, what do you mean by
"more elegant" "Barb Reinhardt" wrote: =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. -- HTH, Barb Reinhardt "prattmic" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average rating by name.
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" wrote: 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average rating by name.
Nevermind, I found the COUNTIF function.
"prattmic" wrote: 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" wrote: 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average rating by name.
=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. -- HTH... Jim Thomlinson "prattmic" wrote: 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" wrote: 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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Average rating by name.
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 "Barb Reinhardt" wrote: =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. -- HTH, Barb Reinhardt "prattmic" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Rating according to 2 criteria. | Excel Worksheet Functions | |||
Rating | Excel Worksheet Functions | |||
rating cells 1-5 colour coded HOW? | New Users to Excel | |||
RAG Rating | Charts and Charting in Excel | |||
Rating 1 to 10 | Excel Discussion (Misc queries) |