LOOKUP/AVERAGE problem

[JjL]

...as i mentioned in my other thread, i am only a beginner to excel

My project is centered around the results and statistical performenc
data relating to the soccer results of one team.
Here i am trying to work out an average with a lookup in the sam
formula.

Basically, the sheet just stores the statistical performence data o
each player against different teams. I would like to work out som
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 formul
wouldn't work as a system, as new statistics are input on a weekl
basis for each player. A LOOKUP would work systematically though...

I can use a LOOKUP in its simplest form to calculate the total goal
scored for a player, but i struggling to return the amount of goal
scored per game; that is, the amount of goals scored divided by th
amount of games played. For example, here, McAteer has played 4 game
and scored one. I would like the value 0.25 to be returned, if at al
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 b
tomorrow or im in trouble!

Thank you in advance,

Jj :

 

[Peo Sjoblom]

Try this

=AVERAGE(IF(D18="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((D18="McAteer")*(E1:E8="Jason"),G1:G8))

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

=AVERAGE(IF((D18=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]

thanks for your response again!

This formula is modelled on the last example you gave to me:
=AVERAGE(IF((F3:F34=AA2)*(G3:G34=AB2),I3:I34))

I am however regretable to inform you that, again, i am returning
#VALUE!

when i did show evaluation steps>evaluate formula it informs me tha
the problem is that -'the cell currently being evaluated (AA2) contain
a constant'- And the same for AB2.

I do not know what this means


Thanks for you help though! I think we are on the right track

Jj :

 

[Peo Sjoblom]

You need to enter the formula with ctrl + shift & enter (it's an array
formula), select the cell with the formula, press F2, then press ctrl +
shift & enter

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon