|
Does anyone in here have an extensive knowledge of Excel? I'm trying to build a database and I'm having a little trouble.
My eventual goal for one of the pages is to create a system that does the following steps:
1. Contains a list of Players, Fantasy Costs, and Scoring. (Complete)
2. Contains a formula to generate random numbers based on the number of players. (Complete)
3. Takes the random number generated, points to that particular Cell in the spreadsheet, and returns the value of that cell... ie: Say I random 16, I want to return the value of Cell C16. (Complete)
4. Compile a function that has a maximum cost associated to it which generates M players that cost no more than N. (Incomplete)
5. Generate the total score of that random team. (Incomplete)
Just some hints in the right direction would be amazing, thanks.
|
So.. what's the problem? i was about to put an answer to your first "incomplete" (3) but i see you've edited your post a few minutes later to 'complete' instead.
fwiw i would've used indirect()
excel is one of those things where you just tinker and figure stuff out (although the last 2 steps are just simple math formulas now)
|
Yeah, I got help on IRC about Indirect. Thanks anyways.. kind of a useless post now. Hope to finish this up sometime tomorrow for another massive MSL thread.
|
Calgary25989 Posts
If you link the Excel file and are a little more specific I can definitely help. I've spent a lot of time on reasonably complex Excel functions and macros.
|
I can't post the spreadsheet right now, but here's what I'm working on..
Cells A1-A32 = Player Names
Cells B1-B32 = Player Cost
Cells C1-C32 = Amount Scored
Cells F1-F5 =Randbetween(1,32)
Cells G1-G5 =INDIRECT("B"&F1-F5)
Cells H1-H5 =INDIRECT("A"&F1-F5)
Cells i1-i5 =INDIRECT("C"&F1-F5)
I'm generating a list so far that has the following components:
Player Cost | Player Name | Player Score
Player Cost | Player Name | Player Score
Player Cost | Player Name | Player Score
Player Cost | Player Name | Player Score
Player Cost | Player Name | Player Score
All these returned components are based on 5 randomly generated numbers which assign me to one of the 32 players in the list.
My next function must sum the cost of the players, if the cost is less than or equal to 10, then I need to print the 5 player's names and scores to 5 new cells on Sheet 2, A1-A5, if not - nothing
The next time I run the function I need the names and scores to be printed on A6-A10, etc.
Then I'm going to sort sheet 2 based on the highest sum of the scores in column B.
I want to eliminate any duplicate teams on Sheet 2.
None of this is especially difficult, I just couldn't find that damn INDIRECT function. If you still want to help and save me some time learning functions.. feel free. It's more time to spend on the other thing I'm working on.
|
Have you worked with V-LOOKUP?
|
United States17042 Posts
On February 07 2009 10:19 Kunty wrote:
Have you worked with V-LOOKUP?
this is easily the most powerful function that i've worked with. However, it's really tough to troubleshoot, and it can get super frustrating. If you do this you need to figure out how to name ranges, and it's sometimes easier to learn how to write your own macros
|
Calgary25989 Posts
Maybe I'm stupid but I can't imagine it without seeing the spreadsheet ;_;
|
I'm thinking my idea is easier to do in C++ I'm exploring those options at the moment. I can design a decent GUI in VB and code it up.
Had to leave for a while, the US Navy wants to hire me to go to sea for 5 months a year. WTF. -_-
|
If you just want to find a random team costing no more than N, you should look into "Goal Seek" and "Scenario Manager". They are under the "Data" tab in the Excel2007 Ribbon.
|
|
|
|
|
|
EPT
