Excel - Looking for a formula please 2

[Kenja824_home]

This is for an excel spreadsheet I am creating for my fantasy football league.


In the attached file, there are three tabs. The LEAGUE tab is all auto filled using formulas. The PLAYERS tab is the one I update when I need to.

I would like to change the formulas I am currently using to be a bit more efficient than they are.

Currently if a team owner drops a player, adds a new player or trades a player, I need to adjust the owner names (which is no problem) and change the number listed in column D. THis column is added to the Concatenate in Col.A. I would like to in the League TAB to only show QB and remove the column D in the PLAYERS tab.


I need a formula for C6 in the LEAGUE tab. It will look for B6 in col B in the PLAYERS tab. It will also look for the Owners name (C2) in col L of the PLAYERS tab.
There will be anywhere from 1 to 12 players who will match these two columns. I want it to look in col C (Rank) in the PLayers tab and return the option that has the lowest rank.

Then in the next sell, C7, I want it to do the same thing but this time return the player with the 2nd lowest rank.


****

I know that VB coding would be much more efficient than formulas here. If I was even halfway decent with VB coding, I would use it. Believe me when I tell you that I have tried to grasp coding. It is the only thing I have ever set my mind to learning that I could not learn enough to use it. If someone wants to build a form of this using the code, I will be glad to use it, but for building it myself, I am limited to formulas.


Thank you in advance for any help. And I am sorry if I wrote this confusingly. I have that problem sometimes. I tend to make things more confusing the more I try to clarify them. lol

 

[Kenja824_home]

I have tried twice to upload my file but it doesnt seem to show it is uploaded.


Here is the link address it gives when I upload it. In case you cant see anything.

https://files.engineering.com/getfi...4617-9eb3-e3d84c60e473&file=Sleeper-Test.xlsx

 

[JG2828]

I wasn't sure how you wanted to display the data on the League tab so I added it to the Players tab by inserting new columns T and U after existing column S.
T4: =INDEX(SORT(FILTER($A$4:$L$249,$L$4:$L$249=$S4,0),3,1),1,5)
U4: =INDEX(SORT(FILTER($A$4:$L$249,$L$4:$L$249=$S4,0),3,1),2,5)

 

[Kenja824_home]

Hi JG2828

Thanks for your time on that. Unfortunately there is a major problem. One I neglected to mention. I am using Microsoft 2007 I believe it is, and I dont have those formula commands. For some reason I was thinking that someone looking at my file would see the same version. I dont know why I thought that and I know better than that. Just got stupid.

Tonight I was playing with it and figured out how to do it. I used a SMALL(IF function with some concatenates. I would not be surprised in the least if there is an easier way to do it, but it worked. lol

Attaching my file just in case anyone wishes to look at it.

The main formulas were on the LEAGUE tab filling out the players name and Ranking. After that the rest of the info is reached with simple formulas.

 

[IDS]

The post here:

https://newtonexcelbach.com/2009/03/23/a-sort-function/

has a link to a spreadsheet with VBA code for a sort function. It should work with JG2828's Index functions, but I haven't tried it.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[3DDave]

Example #24,398 of a spreadsheet being used where a database is appropriate.

I think that every engineering degree should require a semester of creating databases and queries and as many classes after that requiring the classwork to be submitted as a database so that, perhaps one day, Excel can get back to being a spreadsheet for equations and data management can go elsewhere. It's not like all the effort to recreate database functions is a big help.

 

[IDS]

I really don't see how a spreadsheet for a fantasy football league with a few hundred data items is something where a database program would do a better job, especially if the person setting it up is more familiar with spreadsheets.

And having a function that will do a live sort on any range in a spreadsheet without setting it up as a table has many uses outside database applications.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[3DDave]

"Better job" is relative. I have seen massive bastard creations of millions of formulas when a simple table and small amount of SQL would have done the job just as well without the worry that some error in the middle of that spreadsheet was made, poisoning the output and being very difficult to find.

Microsoft keeps doing database-cripple efforts like Pivot Tables and now the Sort and Filter functions and doing array outputs for individual formulas (which I cannot help but believe that behind the curtain they populate the sheet dynamically with even more formulas, hidden from the user.)

People don't use databases because they are unfamiliar with databases and so they start building little projects that look like databases in Excel until they become unmanageable wads of spit and chewing gum.

At the very least, SQL is rather readable without requiring a large amount of indecipherable references:

INDEX(SORT(FILTER($A$4:$L$249,$L$4:$L$249=$S4,0),3,1),1,5)

It's been a long time since I tried SQL, so I won't try to write the equivalent. It's a huge opportunity that engineering has missed.

Oh - and where would it make a difference? In the very many papers on genetics that used Excel to process the data, but down in the thousands of rows Excel decided to replace gene names with dates, so the sorting and counting didn't work. Know what application doesn't change the data because Redmond is smarter than users? Databases.

Not that I haven't had fights with smaller data and Excel's maniacal need to convert data types without asking.

 

[SkipVought]

Dave, I both agree snd disagree.

I fortunately discovered that Excel also had database-like features as well, back in the 90s, with Oracle Transparent Gateway and Microsoft Query. I could tap Oracle Tables and access "near real-time data" rather than wait for a programmer to run an extract.

Then I discovered that sheets (tables) within my workbooks were accessible to query and join using SQL.

So in my final job at a major aircraft manufacturer, I introduced them to this expanded view of data acquisition and analysis. Had access to rehosted tables from SAP, SQL Server network of fabricating machines, Access DBs from IEs for instance.

But I would agree that although the potential is great, the understanding of how to access, manipulate and maintain data is generally lacking in Excel users.

Managers generally want data that's not readily available in canned reports. With the proper access, Excel can acquire and produce such reports. Excel can be viewed as a quick source for new reports or the breadboard for proof of concept and requirements prior to formal IT generation.

On project that exemplifies these principles was that of generating tool orders for use in 5-axis milling machine tool carousel and minimizing carousel tool changes, using data from the machine, SAP shop orders, Access DB Tool Inventory and provided a proof of concept that was then used by IT to generate a corporate integrated system for general shop floor implementation.

Just my 2 cents.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Ken, it would help if you posted the formula that you used along with the sheet & cell.

We might all learn something.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Kenja824_home]

Hey Skip

Glad to see you pop in.

I did attach my actual excel file to my last comment. It probably just got lost in the shuffle. Here it is again.

The main formulas were on the LEAGUE tab filling out the players name and Ranking. After that the rest of the info is reached with simple formulas.

 

[Kenja824_home]

Just to be clear, as mentioned in my last comment as well, I did get it working.

 

[BridgeSmith]

I'm just curious, do you run a league outside of one of the online hosts? It looks like much of what you're tracking is available through Yahoo, and I think ESPN is similar. If you do run the league independently, I'd like to ask why?

I am the Commish for a private Yahoo league, and all of the in-season stuff is managed through them. I put together some Excel sheets to sort out draft prospects for myself, and manage the settings, but everything else is managed and tracked by Yahoo.

 

[Kenja824_home]

Hi BridgeSmith

No, I stopped running leagues of my own when ESPN got into the fold. lol What it is, is a combination of a couple things.

1) Im a geek like that. I am always using Excel to create lists for Fantasy Football.

2) The main league I am in these days is a dynasty league in Sleeper. Sleeper, IMO, really is subpar when it comes to the formatting. I cant even get all the starters to show up on one screen because there is so much senseless fat around each player. Because this league loves to make a lot of trades during and off season, I wanted a way to see all the players for each team on one screen to help me see possible trades. I wanted to be able to see the stats I want to see, that doesnt immediately show up in league pages. Like their ages, 40 time and bye weeks.

The thing is, stuff like their ages, that keeps changing. So I found a list of all the players and their birthdates on-line and merged it in. Now all of their ages will update automatically. Not sure if you are familiar with Dynasty format, but we keep all of our team every year and draft only rookies. Since you keep your players, their ages take a larger part in trades. In a redraft league, I would take Henry over most RBs. In this league he loses a lot of value as he likely only has a year or two left before he starts dropping off the table.

 

[BridgeSmith]

I do strictly a redraft league, but I've heard good things about Yahoo's setup for keeper and dynasty leagues, also. You may want to check it out.

I get alot of stats and rankings from Fantasypros and Yahoo. I copy and paste into an Excel sheet I built. Nothing fancy, just a few formulas to separate the players by position.