Try this

=AVERAGE(IF(D1:D8="McAteer",G1:G8))

entered with ctrl + shift & enter, for better usability replace the name of
the player with a cell where you would put the name, that way you won't need
to edit the formula every time you change the player. In case the player has
a common surname and there could be more than one player on the team with
the same name you could add the forename or the position (of course the
latter means that the player has to have the same position in all matches)

=AVERAGE(IF((D1:D8="McAteer")*(E1:E8="Jason"),G1:G8))

as I said earlier better to use cells as criteria when changing the names

=AVERAGE(IF((D1:D8=I1)*(E1:E8=J1),G1:G8))

it also makes the formula smaller
--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"JjL" <[email protected]> wrote in message
news:[email protected]...
>
> ..as i mentioned in my other thread, i am only a beginner to excel
>
> My project is centered around the results and statistical performence
> data relating to the soccer results of one team.
> Here i am trying to work out an average with a lookup in the same
> formula.
>
> Basically, the sheet just stores the statistical performence data of
> each player against different teams. I would like to work out some
> averages of these results if possible.
>
>
>
>
> Column A: Opposition
> Column B: Date
> Column C: Squad Number
> Column D: Surname
> Column E: Forename
> Column F: Position
> Column G: Goals Scored
>
>
> Barnsley______6/8/2005___6__McAteer__Jason __midfielder__0
> Blackpool _____1/10/2005__6__McAteer__Jason__midfielder__1
> Bournemouth__7/11/2005__6__McAteer__Jason __midfielder__0
> Bradford______16/2/2006__6__McAteer__Jason __midfielder__0
> Barnsley______6/8/2005___9__Davis____Steve__attacker___2
> Blackpool _____1/10/2005__9__Davis____Steve__attacker___1
> Bournemouth__7/11/2005__9__Davis____Steve__attacker___0
> Bradford______16/2/2006__9__Davis____Steve__attacker___1
>
>
> I realise that i could do a simple =AVERAGE() here, but such a formula
> wouldn't work as a system, as new statistics are input on a weekly
> basis for each player. A LOOKUP would work systematically though...
>
> I can use a LOOKUP in its simplest form to calculate the total goals
> scored for a player, but i struggling to return the amount of goals
> scored per game; that is, the amount of goals scored divided by the
> amount of games played. For example, here, McAteer has played 4 games
> and scored one. I would like the value 0.25 to be returned, if at all
> possible.
>
> The closest i can get is:
> =LOOKUP(Surname,Surname,GoalsScored)/
>
> except i dont know how to divide it by the amount of games played
>
> Again, sorry about my lack of jargon or general technical knowhow.
>
> Any help would be massivley appreciated as i need this sorted by
> tomorrow or im in trouble!
>
> Thank you in advance,
>
> Jj
>
>
> --
> JjL
> ------------------------------------------------------------------------
> JjL's Profile:
> http://www.excelforum.com/member.php...o&userid=32597
> View this thread: http://www.excelforum.com/showthread...hreadid=523984
>