Any MICROSOFT EXCEL experts?

DonDollars · Nov 1, 2006

I am developing a program for NBA basketball using points per possessions to determing "starting point lines" for the NBA. I have used this handicapping method in the past with success, however, this year I am trying to add some upgrades and improvements.

Up until this point I have done everything MANUALLY using Excel and it takes a LONG, LONG, LONG time. I am wondering if anyone is an EXCEL genius and could possibly help me out.

I have a worksheet that has a tab for each team. In the team's tab it has a column for Date, Opponent, H/A, Score, among MANY other things.

One of the columns is "EXPECTED POSSESSIONS" another is "EXPECTED OFFENSIVE POINTS PER POSSESSION" another is "EXPECTED DEFENSIVE POINTS PER POSSESSION".

I also have another tab that indicates the Teams Expected #'s along with an "Adjustment Factor" based on the team they are playing.

NOW: for each team I could go down into each individual cell for "EXPECTED POSSESSIONS" and put in a function that says:

Add the (Cell for the team + the adjustment factor for their opponent)

This is WAY TOO TEDIOUS, however, and I cannot "COPY THE FUNCTION" down for every game, because each game is either Home or Away and against a different opponent. Is there a way (maybe if I designate each team a #) that I can set up a function that will determine these "EXPECTED" stats based on whether the game is Home or Away and what # opponent they are playing?

DOES THIS MAKE SENSE? I am in desperate need of help. I believe there is a way I can be saving hours doing this....I just need some Excel Assistance. I could send part of the sheet if that would make it easier to understand.

wolfie_cr · Nov 1, 2006

I will be happy to give you a hand but I am not going to do a thing about it today, if by tomorrow you still need help send me a sample of what you want to achieve , ask a mod to relay me your email addy and I will send you mine

JustWin · Nov 1, 2006

It sounds like you need a vlookup function...

get my email from wil and send me part of it

Tulsa · Nov 1, 2006

I had a similar problem with the baseball statistical data base I am keeping. I enter each game twice and H or A is a value. I don't use numbers for the teams. But enter the data in TWO different rows for one game....one row for each team.

xxxxxxxxxxxxxxxxxxxxxxxxxxxxx yyyyyyyyyyyyyyyyyyyyyyyy

yyyyyyyyyyyyyyyyyyyyyyyyy xxxxxxxxxxxxxxxxxxxxxxxxxxxxx

It is never easy to enter data BUT, once I have the game entered on one row, I just copy the values and paste them in the next row in reverse order.....let me show you:

<TABLE style="WIDTH: 2014pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=2680 border=0 x:str><COLGROUP><COL style="WIDTH: 104pt; mso-width-source: userset; mso-width-alt: 5046" width=138><COL style="WIDTH: 91pt; mso-width-source: userset; mso-width-alt: 4425" width=121><COL style="WIDTH: 81pt; mso-width-source: userset; mso-width-alt: 3949" width=108><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4059" width=111><COL style="WIDTH: 110pt; mso-width-source: userset; mso-width-alt: 5339" width=146><COL style="WIDTH: 113pt; mso-width-source: userset; mso-width-alt: 5485" width=150><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3913" width=107><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4059" width=111><COL style="WIDTH: 79pt; mso-width-source: userset; mso-width-alt: 3840" width=105><COL style="WIDTH: 95pt; mso-width-source: userset; mso-width-alt: 4608" width=126><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4169" width=114><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4059" width=111><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 4315" width=118><COL style="WIDTH: 82pt; mso-width-source: userset; mso-width-alt: 3986" width=109><COL style="WIDTH: 87pt; mso-width-source: userset; mso-width-alt: 4242" width=116><COL style="WIDTH: 109pt; mso-width-source: userset; mso-width-alt: 5302" width=145><COL style="WIDTH: 113pt; mso-width-source: userset; mso-width-alt: 5485" width=150><COL style="WIDTH: 84pt; mso-width-source: userset; mso-width-alt: 4096" width=112><COL style="WIDTH: 88pt; mso-width-source: userset; mso-width-alt: 4278" width=117><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 4315" width=118><COL style="WIDTH: 105pt; mso-width-source: userset; mso-width-alt: 5120" width=140><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3913" width=107><TBODY><TR style="HEIGHT: 42pt; mso-height-source: userset" height=56><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 104pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 42pt; BACKGROUND-COLOR: transparent" width=138 height=56>Date</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 91pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=121>Side # (Daily)</TD><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 81pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=108>W/L Margin</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 83pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=111>Home/Away</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 110pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=146>Team Name</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 113pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=150>Pitcher</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 80pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=107>Right/Left</TD><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 83pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=111>Money Line</TD><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 79pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=105>-1.5 / +1.5</TD><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 95pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=126>Run Line Price</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 86pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=114>Own Score</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 83pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=111>Total O/U</TD><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 89pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=118>Over Price</TD><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 82pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=109>Under Price</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 87pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=116>Total Runs Scored</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 109pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=145>Opposing Team Name</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 113pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=150>Opposing Pitcher</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 84pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=112>Opposing Right/Left</TD><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 88pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=117>Opposing Money Line</TD><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 89pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=118>Opposing +1.5/-1.5</TD><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 105pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=140>Opposing
RL Price
</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 80pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=107>Opp Score</TD></TR><TR style="HEIGHT: 18.75pt" height=25><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 18.75pt; BACKGROUND-COLOR: transparent" height=25 x:num="38941">8/12/2006</TD><TD class=xl27 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num>1</TD><TD class=xl28 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">-1</TD><TD class=xl27 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">A</TD><TD class=xl27 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">SF Giants</TD><TD class=xl27 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">M Cain</TD><TD class=xl27 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">R</TD><TD class=xl28 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">+1.30</TD><TD class=xl28 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">+1.5</TD><TD class=xl28 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">-1.65</TD><TD class=xl27 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num>5</TD><TD class=xl27 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num>8.5</TD><TD class=xl28 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">-1.15</TD><TD class=xl28 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">-1.05</TD><TD class=xl27 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num>11</TD><TD class=xl27 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">LA Dodgers</TD><TD class=xl27 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">B Penny</TD><TD class=xl27 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">R</TD><TD class=xl28 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">-1.50</TD><TD class=xl28 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">-1.5</TD><TD class=xl28 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">+1.45</TD><TD class=xl27 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num>6</TD></TR><TR style="HEIGHT: 18.75pt" height=25><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 18.75pt; BACKGROUND-COLOR: transparent" height=25 x:num="38941">8/12/2006</TD><TD class=xl27 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num>2</TD><TD class=xl28 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">+1</TD><TD class=xl27 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">H</TD><TD class=xl27 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">LA Dodgers</TD><TD class=xl27 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">B Penny</TD><TD class=xl27 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">R</TD><TD class=xl28 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">-1.50</TD><TD class=xl28 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">-1.5</TD><TD class=xl28 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">+1.45</TD><TD class=xl27 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num>6</TD><TD class=xl27 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num>8.5</TD><TD class=xl28 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">-1.15</TD><TD class=xl28 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">-1.05</TD><TD class=xl27 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num>11</TD><TD class=xl27 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">SF Giants</TD><TD class=xl27 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">M Cain</TD><TD class=xl27 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">R</TD><TD class=xl28 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">+1.30</TD><TD class=xl28 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">+1.5</TD><TD class=xl28 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">-1.65</TD><TD class=xl27 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:num>5</TD></TR></TBODY></TABLE>

goodcall · Nov 2, 2006

JustWin has a good idea...somewhere, I guess on a different sheet (we'll call it "AdjFactor"), have a table of each team in column A and their adjustment factor next to them in column B. Now instead of the function you described, put this in:

=[Cell for team]+VLOOKUP([Opponent],AdjFactor!A$1:B$30,2,FALSE)

The lookup command will search for the opponent in the Adjustment table you just made, and return the value next to it.

Tulsa's idea is probably better, but my brain is too fried right now to understand it. I'll take another look in the morning.

BTW Don D, I have a very similar spreadsheet, so if you have any other questions, feel free to shoot me an email.

Labeeb · Nov 2, 2006

Sounds like the guys have you pointed in the right direction. VLOOKUP is definitely the way to go.

DonDollars · Nov 2, 2006

Wow. Thank you for the replies. I am finishing off the "manual" parts that need to be tweaked for this year, and then I will probably send it out. Thank you!