Calculating individual player performance for 2 different teams - sumproduct or vlookup??

[J_rice2004]

Hi there,

This is a rewrite of my original post to try get straight to the point, i would appreciate some help please.

I need a formula or function that can:

- Calculate number of wins, draws, losses for a given player.
- Calculate goals scored for and goals against for a given player.
- Show the results history for the last 5 games that each given player has played in as a graphical representation (Green cell = Win, Yellow=Draw, Red=Loss).

The caveat with the above, is that each player is picked manually each week to represent one of two teams, either the reds or whites. They don't constantly represent the same team. There is a pool of 30 players and only 20 play each week, these 20 are manually selected and inputted into the workbook on worksheet 2.

I would like the only manual input to be the selection of the 20 players and then the goals scored for each team in order to calculate which team won. I would like the rest of the calculations to be automatic on the first worksheet in the player league table i have created.


I have attached the workbook i have created so far to assist in understanding the problem.

Many thanks for the help.

Jamie.

[FDibbins]

Hi, welcome to the forum

I see that you have had at least 22 views of this thread, and no responses

Might I suggest that you try re-wording your requirement, and maybe showing simpler samples?

[AliGW]

You sent me a private message asking for advice. Like Ford, I would suggest rewriting your opening post. It is too wordy: what we need are the essential requirements listed, perhaps as bullet points. I have not yet looked at the attachment.

[J_rice2004]

Hi Ali, i have re-written and hope that this is more straight to the point.

Please can you advise?

Jamie.