Need to lookup 2 cell values and get average from 1

[dubsrus]

afternoon all, looking for some help with excel which i'm sure is relatively easy.....if you know how . i've just started to use excel and i'm struggling to work out something.

setup i have is 2 sheets and in sheet 1 i have a range called "names" (collum A) and another called "severity" (Collum B). the "names" range contains the names of people and the severity contains numbers between 1-10.

for example

Name - Severity

Stephen - 1
John - 8
Stephen - 2
Stephen - 3
John - 7
Mike - 5

from the above i need to know the average severity figure everyone has and output it somewhere.

open to suggestions the best way to go about this as newbie

p.s great site and thanks for help.

cheers

[NBVC]

Please read our forum rules first. Then amend your title according to Rule # 1

[dubsrus]

sorry, ammended. wasn't sure what to put as not sure best way round this.

cheers

[DamienC]

=Average(B:B) gives you the overall average. Just don't put it in column B.

I presume you want individual averages though...

[dubsrus]

damien, thanks for reply.

yes i need to work out the average based on if the name is "stephen" for example.

the little example above would output stephen has average of 2 and so on for each person.

cheers

[NBVC]

sorry, ammended. wasn't sure what to put as not sure best way round this.

cheers

Thank you.

[NBVC]

You can use a pivot table...

or

if you list the unique names somewhere on the sheet... you can use formula:

=Sumif($A$1:$A$100,X1,$B$1:$B$100)/Countif($A$1:$A$100,X1)

where A1:B100 contains your original data and X1 contains one of the unique names... then you would copy down the formula.

Note: adjust ranges and references to suit.

[dubsrus]

thanks for the reply. i will try that.

cheers