formula for fantasy baseball!

[drnick]

hello, this is my first post into this forum, I did some searching and found people using excell to do NCAA bracket scoring and such, but nothing for what I'd like to do. I know a little about excel but I can't figure out what forumla to use for this.

I'm in a fantasy baseball auction league, without a big long rundown on what that entails I'll give the cliff notes version of it. Basically, every team has a CAP of 75 dollars to spend on players. players are then "auctioned" off to each team. bidding in 50 cent increments. I'd like to be able to keep track of all of this in Excel, players teams have already aquired, how much they paid for them, how much cap they have left, and what their MAX bid would be

The problem I'm having is with the MAX bid part. because you need a minimum of 22 players (and can have up to 27 players) it makes things tricky. so if a team is bidding on its first player (still has their full 75 dollars left) their maximum bid would be 64.50 (if they bid 64.50 on 1 player, theyd have just enough money to fill their roster with 21 players at 50 cents each).

I tried to attach a file of the excell sheet, but it won't allow that type of attachment, I attached a jpg pic so you can at least see what I'm talking about. I'd be more than happy to E-mail you the sheet if youd like as well


thanks in advance!
-Nick

[BigBas]

Your best bet is to post the actual file. In order to do that, you must convert it to a .zip format, then attach.

[drnick]

haha, why didn't I think of that, attached is the .zip of the file

[Paul]

Well, this isn't the prettiest formula, but it seems to work..

Put this in B2 to determine your MAX bid:
=IF(COUNTIF(C4:C25,"<>0")=22,-B3,IF(B3>=0,0,75-(SUM(C4:C25)+((22-COUNTIF(C4:C25,"<>0"))*0.5)-0.5)))

Because you are only required to have 22 players, the calculations should only pertain to the first 22 rows, but incorporate checks to see if you still have money left over after 22 are bought. I think this does that fairly well, but I'll let you be the judge.

Broken down, this formula works as follows:

1. "IF(COUNTIF(C4:C25,"<>0")=22,-B3,"

This checks to see if the first 22 cells (players) have an auction value greater than 0 (since .50 is the minimum bid to win a player). If the minimum 22 players have been purchased, then the MAX bid amount is set to the negation of the CAP amount (which is the amount you have left under the CAP). This means that you could theoretically spend the rest of your CAP money on one player, without reserving .50 for additional players. If the first 22 cells (players) don't have a value over 0 in them the second part of the formula is triggered.

2. "IF(B3>=0,0,75-(SUM(C4:C25)+((22-COUNTIF(C4:C25,"<>0"))*0.5)-0.5)))"

This checks to see if B3 (your CAP) is at 0, and if so it sets your MAX bid to 0. If, however, you still have $ left to buy players it then does the following:

TOTAL CAP - (CURRENT $ SPENT + [(TOTAL REQUIRED PLAYERS - COUNT OF ALREADY BOUGHT PLAYERS) x MIN COST PER PLAYER] - MIN COST OF NEXT BID PLAYER

Once you have this formula in B2, you can then copy it to D2, F2, H2, J2...etc.

Let me know how that works for you...

[drnick]

that works Great!. I really appreciate the quick help. thanks so much
-Nick

[Paul]

Glad I could help.. this actually gave me a few ideas for my fantasy football league's auction.

[drnick]

yeah, I think its a huge advantage in an auction to know what everyone else can bid... and also what they need to spend money on.

I can't believe people still do drafts, auctions are so much more fun.

[likkerding]

hi Paul trying to figure out a formula for my fantasy hockey pool - tried to follow but got lost somewhere in the shuffle! mine is as simple as this becasue i have different cell values cant figure out what is what - any help is appreciated ps how do i upload my file to you?

[protonLeah]

likkerding,
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

---

---

To attach a Workbook

• Click Advanced (next to quick post),
• Scroll down until you see "Manage Attachments",
• Click that then select "add files" (top right corner).
• Click "Select Files" find your file, click "open" click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Click "Done" at bottom right to close the Attachment Manager.
• Click "Submit Reply"