Thread: Need to lookup 2 cell values and get average from 1

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

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

Originally Posted by dubsrus

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

cheers

Thank you.

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.