How to determine the best fantasy team based on average scores from a squad of players

Hi there guys,

I want to determine the best combination of players easily from a squad of players based on what "position" they play in.

There are five different playing "positions" a player can be in and each player can score points based on what stats he gets for a particular position. i.e each player can have up to 5 different scores theoretically.

I have a squad of 20 players and we only choose a team of 8 from that 20. I want to determine the best combination of players by position that will maximise my score. The team has to consist of a set combination of positions.

For ease of explanation (hopefully) I will list them as position A,B,C,.. etc.

The team needs to consist of:

2 x position A players
1 x position B player
3 x position C players
1 x position D player
1 x position E player



Is there a way to easily find the best combination of player scores from the squad of players? Is there a macro that can do this for me or a formula that i can plug variables into??

Help I'm quite new to excel.

I already have a spreadsheet determining player scores, with averages by position. It also has a look at consistency of scores etc.

Many Thanks Andrew

Welcome to the forum Andrew!

Any chance you could upload your workbook? It's much easier for us to help you with a solution that way, rather than trying to visualize how your data is structured. If there's any sensitive data in there, just put in some dummy numbers. We also don't need to see the whole workbook, just what is relevant to your problem here.

Hi there,

Thanks for the reply. The uploaded doc is derived from another workbook but you can see what I mean about players and positions - hopefully!

I basically need a formula or macro if possible to determine the best combination of players to maximise team score. The team requires:

2 x Forwards
1 x Marker
3 x Midfielders
1 x Tackler
1 x Utility

Many thanksUploaded Version HFFL.xlsx