software

...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 ScoredBarnsley______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___1I 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: www.excelforum.com/member.php...oamp;userid=32597
View this thread: www.excelforum.com/showthread...hreadid=523984Try this

=AVERAGE(IF(D18=quot;McAteerquot;,G1:G8))

entered with ctrl shift amp; 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=quot;McAteerquot;)*(E1:E8=quot;Jasonquot;),G1:G 8))

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

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

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon

quot;JjLquot; gt; wrote in message
...
gt;
gt; thanks for your response again!
gt;
gt; This formula is modelled on the last example you gave to me:
gt; =AVERAGE(IF((F3:F34=AA2)*(G3:G34=AB2),I3:I34))
gt;
gt; I am however regretable to inform you that, again, i am returning a
gt; #VALUE!
gt;
gt; when i did show evaluation stepsgt;evaluate formula it informs me that
gt; the problem is that -'the cell currently being evaluated (AA2) contains
gt; a constant'- And the same for AB2.
gt;
gt; I do not know what this means
gt;
gt;
gt; Thanks for you help though! I think we are on the right track
gt;
gt; Jj
gt;
gt;
gt; --
gt; JjL
gt; ------------------------------------------------------------------------
gt; JjL's Profile:
gt; www.excelforum.com/member.php...oamp;userid=32597
gt; View this thread: www.excelforum.com/showthread...hreadid=523984
gt;