Formula Generator | Excel

Fantasy Football Draft Tracker in Excel

Create an Excel-based Fantasy Football Draft Tracker to manage available players for QBs, RBs, and WRs. Track drafted players in real-time using formulas to filter available options as drafts occur.

Prompt

create fantasy football draft tracker. There are 3 positions. QB, RB and WR. There are ten players per position. If a player gets drafted, the player should be taken out list of players available to choose from  based on position selected.

Answer

Fantasy Football Draft Tracker in Excel

This document outlines how to create a Fantasy Football Draft Tracker using Excel, specifically for tracking 3 key positions: Quarterbacks (QB), Running Backs (RB), and Wide Receivers (WR).

Overview

The tracker will maintain a list of available players across the specified positions and will remove players from the list as they get drafted.

Structure

1. Setup the Excel Spreadsheet

Sheet Layout:

Sheet 1: Players
- Column A: Position (QB/RB/WR)
- Column B: Player Name
- Column C: Drafted Status (Yes/No)
Sheet 2: Draft Tracker
- Columns for Positions (QB, RB, WR)
- Lists of players who remain available after each draft pick.

2. Input Example Data

Players Sheet (Example):

Position	Player Name	Drafted Status
QB	Player 1	No
QB	Player 2	No
RB	Player 3	No
WR	Player 4	No
...	...	...

3. Create the Draft Tracker Formula

To dynamically remove a drafted player from the available list based on their position, use the following array formula in the Draft Tracker sheet:

Formula to Get Available Players:

For QBs Available:

=FILTER(Players!B:B, (Players!A:A="QB") * (Players!C:C="No"))

For RBs Available:

=FILTER(Players!B:B, (Players!A:A="RB") * (Players!C:C="No"))

For WRs Available:

=FILTER(Players!B:B, (Players!A:A="WR") * (Players!C:C="No"))

4. Formula Explanation

FILTER Function: This function will generate a dynamic array based on specified criteria.
- Players!B:B: This specifies the range to return values from; in this case, player names.
- (Players!A:A="Position"): This condition checks if a player's position matches (QB/RB/WR).
- (Players!C:C="No"): This condition confirms that the player has not yet been drafted.
Multiplication (*): Using multiplication acts as an AND operator in array evaluations.

5. Implementation of the Draft Tracker

Drafting a Player: When a player is drafted, update the Drafted Status column in the Players sheet to "Yes".
Refreshing the Available Players List: The formulas in the Draft Tracker sheet will automatically update to show the available players as the status changes.

6. Practical Example

Given the following in the Players Sheet:

Position	Player Name	Drafted Status
QB	Tom Brady	No
QB	Patrick Mahomes	No
RB	Derrick Henry	No
WR	Davante Adams	No
RB	Nick Chubb	No

After drafting "Tom Brady", update the Drafted Status for Tom Brady to "Yes".

Revised Players Sheet:

Position	Player Name	Drafted Status
QB	Tom Brady	Yes
QB	Patrick Mahomes	No
RB	Derrick Henry	No
WR	Davante Adams	No
RB	Nick Chubb	No

Available Players for QBs:

After updating, the available QB list will be:

QB Available
Patrick Mahomes

Conclusion

This Excel setup allows one to effectively track drafted players and available options for the QB, RB, and WR positions in a fantasy football league. Adjustments to the drafted status lead to real-time updates in available player lists, enhancing the drafting experience.

For a more comprehensive understanding of Excel functionalities, consider exploring the courses available on the Enterprise DNA Platform.

Description

Create an Excel-based Fantasy Football Draft Tracker to manage available players for QBs, RBs, and WRs. Track drafted players in real-time using formulas to filter available options as drafts occur.