This may be complicated to explain so bear with me
I have a spreadsheet full of movies, each with a rating out of 100 and the year they were released. What I want to do is create a chart which shows the average rating based on the year.
I.E. It will take all the ratings from a particular year and give it an average.
Does anyone have any ideas to how I could do this?
AVERAGEIF is your friend here.
Have a look at the yellow cells in the attached for an example.
BSB
That worked exactly how I wanted it to, thanks very much!
No problem. Happy to help.
If you feel the urge to further refine, i.e. average rating of horror movies from 1976, you can add more criteria with AVERAGEIFS.
Don't forget to mark the thread as SOLVED if you're happy you have your solution.
BSB
Oh and I'll say it before someone else pops up with it, you could also use Pivot Tables to do this.
BSB
Something like this...
Data Range
This array formula** entered in E2 and copied down:
=AVERAGE(IF(YEAR(A$2:A$15)=D2,B$2:B$15))
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
You may want to round the results. If so use this array formula**:
=ROUND(AVERAGE(IF(YEAR(A$2:A$15)=D2,B$2:B$15)),0)
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks