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 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

[Peo Sjoblom]

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
>

[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 a #VALUE!

when i did show evaluation steps>evaluate formula it informs me that the problem is that 'the cell currently being evaluated (AA2) contains 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




"JjL" <[email protected]> wrote in message
news:[email protected]...
>
> 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 a
> #VALUE!
>
> when i did show evaluation steps>evaluate formula it informs me that
> the problem is that -'the cell currently being evaluated (AA2) contains
> 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
>
>
> --
> JjL
> ------------------------------------------------------------------------
> JjL's Profile:
> http://www.excelforum.com/member.php...o&userid=32597
> View this thread: http://www.excelforum.com/showthread...hreadid=523984
>