Help managing data in Excel

[Italian Stallion]

Currently, I have several spreadsheets with NBA data.

1. Schedule Data - The date, the two teams, home/away, and the final score of each game. That information is imported. I manually enter significant injuries/trades each day, calculate the standard deviation of the teams result's, and days between each start so I can quickly see who has had a tough schedule and who is well rested and several other things. I have one sheet for each of the 30 teams.

2. Player Data - All critical player information is imported and homemade calculations are done to evaluate player productivity. I have one sheet for each of the 30 teams.

3. Team Data - All critical team data is imported and I calculate power ratings.

4. Odds line Tool - Uses team data from item 3, various adjustments that have to be manually entered from the other spreadsheets, and possible injury data from the day that is all manually entered

Here's my problem. I don't have a way to automatically import all the games scheduled for that day into my odds line tool with the power ratings right next to them. I have to manually cut and paste the team name and ratings into the appropriate slots while looking at the schedule. Then I have to manually enter any other relevant data about injuries, trades, schedule etc... into each line.

There' doesn't seem to be an easy way to work with and format the odds line tool because there is no standard schedule or position that the information will be required.

Can anyone think of a way I can organize my data better or better yet somehow import the schedule and just by the team names, the power ratings and other data can be imported from other sheets automatically?

[stock_t]

Can anyone think of a way I can organize my data better or better yet somehow import the schedule and just by the team names, the power ratings and other data can be imported from other sheets automatically?

If I understand your objective, it seems you could use Access to get there, creating tables for each of your spreadsheets and then linking them by team name. Then design a query limited by a given team to pull in the info you're looking for.
After each team's pull, you'd still have to copy the data into your odds tool, but if you set up your query to pull in all the fields in the order you want (injuries, dates, etc) you would only have one cut & paste per team rather than the multiple you currently have.
Hope that helps.

[mtamada]

Access is a good idea, or any relational database. Excel's great, I use it all the time, but it's limited as a data organizer (as well as a statistical tool).

If we limit ourselves to Excel solutions: if you're not using it already, the vlookup function might help. The teams in the power rating worksheet would have to be sorted alphabetically (even better might be to assign each team a permanent ID number, and sort by that). But then whenever you wanted to grab a team and its power rating, you could use vlookup to tell Excel to get the appropriate data for the appropriate team.

It doesn't automate the process, but semi-automates it. Actually it sounds like your "Schedule Data" table already has all the games and team entered? Then with vlookup all of those teams (current) power ratings could be entered with a swipe of the mouse, assuming that they've been sorted in the Team data worksheet.

[Italian Stallion]

Thanks guys.

I think I made a mistake by using Excel to begin with, but I was already "somewhat" familiar with it and didn't have or know how to use Access. Maybe I'll switch to Access next season.

Perhaps someone can help me with this Excel question.

I can't get this formula to work and I am clueless as to why. It appears in Sheet2

=AVERAGE(IF(Sheet1!$D$2:$D$13=A2,Sheet1!$E$2:$E$13))

In cell A2 on this sheet is the value = NY

In cells D2, D3, and D4 on Sheet1 is the value NY.

In cells D5 through D13 on Sheet1 are other values like ATL, BOS etc...

In cells E1 - E13 on Sheet1 are various numbers.

I want the average of D2, D3, D4 (NY), but it's giving me the average of the entire column of numbers. For some reason it's not matching NY to NY and only using the limited cells I want.

What am I doing wrong?

[xkonk]

Try using =averageif. The first entry would be the range of team names, the second entry would be the single cell that contains NY, and the third would be the range of numbers you want averaged. I think with your example it would be =averageif(sheet1!$D$2:$D$13,A2,Sheet1!$E$2:$E$13).

[Italian Stallion]

Thanks again. I've already been able to make huge improvements.

[agentkirb]

I have kind of the same question. I've been trying to look up an easy way to pull box scores from a website (say, ESPN). You guys say Access is a good way to do it but I'm not that familiar with that program... is there like a good tutorial that explains how to pull data from a website and put it into tables? I've messed with stuff like SQL before so I'm sure it's similar enough that it won't be too hard for me to figure out but I've never coded a database that pulled information from a website before.

[EvanZ]

I don't believe you can scrape the data from the web using Access. AFAIK, you need to write some code to do that. I think what folks are saying is once you have the data, you can use Access to store it or whatever.

[Italian Stallion]

I'm still having a strange problem.

I have a list of my power ratings inside one of my workbooks and a separate workbook for the odds line tool.

If I create a list of my power ratings in my "odds line" workbook directly from the other workbook, then use vlookup to fill those ratings in the other appropriate slots for each game it works great.

If I try to use vlookup directly to bypass the list creation and get them from the power rating workbook, it will not work. I've looked up multiple examples on the web and none of them will work. I keep getting N/A and other errors.

It's not like I can't get by with what I have, but it seems inefficient to have the same information in two locations linked together.

I am using Excel 2003. So maybe that's an issue, but I don't think so.

[mtamada]

A random guess, obviously this is an uneducated guess from afar: make sure you're filling in the arguments of the vlookup function correctly, e.g. by using the mouse to indicate the cells for vlookup to look at. And then make sure that the "looked up" array is specified with absolute rather than relative addressing.

[Italian Stallion]

A random guess, obviously this is an uneducated guess from afar: make sure you're filling in the arguments of the vlookup function correctly, e.g. by using the mouse to indicate the cells for vlookup to look at. And then make sure that the "looked up" array is specified with absolute rather than relative addressing.

That was it.

I have one last question.

It is possible to use vlookup with multiple cells.

In other words, I want to do a lookup by both team name and date, but I have the team name and the date stored in separate cells. One cell has ATL and another has 01/02/12. I want to do that search and come back with "value2"

ATL01/01/12value1
ATL01/02/12value2
BOS01/01/12value3
BOS01/01/12value4

[Italian Stallion]

Never mind. Combining the cells on both sheets seems to work fine.