Excel 2007 Formula to Calculate Mean In a Range

Escanaba
Escanaba used Ask the Experts™
on
Hello,

Hoping someone can assist.  Please see attached example.  Can someone tell me how to put together a formula that looks at the entire range (A1:M13) and will take only the A's in column H and provide the mean score for A's only and place the result in cell P4, then take only the B's and provde the mean score in cell P5, followed by the C's placing the mean score in cell P6?

If this is not possible via formula then a VB solution would be fine too.

Thanks!


EE-Sample.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010
Commented:
It's not clear what you meant.

Excel 2007 introduced AVERAGEIF, which makes this easy.  Assuming you want the average of Col A for which the values in Col H are 'A', this formula works in P4:

=AVERAGEIF($H$2:$H$13,LEFT(O4,1),$A$2:$A$13)

Copy that down through P6.
AnilData Manager

Commented:
Not sure I understand.
How do you define the mean score for A ? Is i the sum of all the sum on that particular rwo ?
Please provide a examples for the mean score for A.


A>
EscanabaManager - HR Analytics

Author

Commented:
Another way to look at this is if you ran a filter on column H for just A's.  Then, run an average for only those rows appearing in column A (filtered for A's only show only 3 rows).  In doing this, with the current example, the A's show a mean of 2.63, B's 2.80 and C's 2.70.  I'm hoping for a formula that can generate these results.

MP - I tried your formua and get the following results:  A 2.33, B 3.00 C 3.00.
Top Expert 2010

Commented:
Escanaba,

Please explain how you got 2.63, 2.8, and 2.7.

Based on the sample data you provided, my formula is right.

Patrick
EscanabaManager - HR Analytics

Author

Commented:
Patrick - you were right.  When I was running the average formula on the filtered data it must of been reading unfiltered data throwing the results off.  When I manually calculated the average it matched what your formula was generating.  Thanks for providing the solution.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial