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

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:G 8))

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" wrote in message
...

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

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

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" wrote in message
...

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