[robfl22]

Hi gang,

I have and entire excel workbook dedicated to Bob McCunes NBA spreads and totals systems. Most of the work is done and alot of the formulas are done. However, I need advice on lookups and how to basically do a prediction page based on the data and McCunes criteria. I want to automate this workbook to where I only have to enter the scores and lines every day and everything else is calculated. Im just a few steps away from completing this project but have hit a wall on what Im trying to do next. Thanks in advance to anyone who is an expert with excel and can help me out.

[Peep]

I like www.mrexcel.com

Using their search has got me by a few "walls".

[dcbt]

without seeing your s/s, it sounds like you probably want to utilize 'vlookups.' maybe enter your matchups on one sheet (ie, away team in A1 and home team in A2), and have vlookups in B1/B2 that look to your calcs on another sheet.

[MJ]

Can you post the spreadsheet for us or is this personal info for your eyes only?

[robfl22]

Attached is the spreadsheet and this is what I want to do that needs to be done in order.

1. According to McCunes Systems, every team must be broken down into a home team and away team. Hence the sheet tabs with names like Atlanta Away, Atlanta Home, Boston Home....etc.etc. That has all been done. One tedious process I am having to do is go the each of these teams and enter the scores and lines on each of there sheets. I want to be able to enter all the scores and lines in the ALL sheet tab and have those scores and lines transferred to each of the teams sheet tabs automatically. If that can get done, that would be huge.
2. On the PREDICTION SHEET tab....this is where the projected totals are calculated. As of now, I am having to enter the yellow areas manually by going to the corrseponding teams sheet tab to obtain the data. I want this to be transferred automatically into those yellow areas according to the date. Right now I have to go to each teams worksheet, find the date of the game, highlight that row and then scroll across until I find the data under 5GameTot, 10GameTot, and 15GameTot.
3. On the MATCHUPSPICKS sheet tab....the very first one. This is where I want the lines to be predicted. I need two bits of information from that of the away team and the home team. You will see how I separate the games by the light gray shading. According to the date on the left, I want the areas highlighted in yellow to be done automatically. Right now, I am having to go to each teams tab and find the team line and the OPR. And that is it.


Keep in mind, all of the calculations on each teams sheet tabs are ALREADY DONE! Nothing needs to be touched. I know this is very hard to explain but I am just a few calculations away from perfecting this sheet. I think once you review the workbook as a whole you might understand what I need done. And of course, if I do get any help on this, this workbook will be made available to anyone that wants it.

Thanks so much in advance for any help you can lend.

[Data]

I am by no means an Excel expert but I think I can help. I am attaching slightly modified spreadsheet that should get you going.

Note, you should start by unifying team names across all the sheets. You should not have Los Angeles (Lk) and LA Lakers, that is a source of future confusion.

Attached is the spreadsheet and this is what I want to do that needs to be done in order.

1. According to McCunes Systems, every team must be broken down into a home team and away team. Hence the sheet tabs with names like Atlanta Away, Atlanta Home, Boston Home....etc.etc. That has all been done. One tedious process I am having to do is go the each of these teams and enter the scores and lines on each of there sheets. I want to be able to enter all the scores and lines in the ALL sheet tab and have those scores and lines transferred to each of the teams sheet tabs automatically. If that can get done, that would be huge.

Look at the "Atlanta Home" and "Cleveland Away" sheets and modify the other sheets accordingly.

2. On the PREDICTION SHEET tab....this is where the projected totals are calculated. As of now, I am having to enter the yellow areas manually by going to the corrseponding teams sheet tab to obtain the data. I want this to be transferred automatically into those yellow areas according to the date. Right now I have to go to each teams worksheet, find the date of the game, highlight that row and then scroll across until I find the data under 5GameTot, 10GameTot, and 15GameTot.

First, you need a date entered (cell A19). Then, see the formulas for Cleveland (away team). The formulas for the home team are very similar.

3. On the MATCHUPSPICKS sheet tab....the very first one. This is where I want the lines to be predicted. I need two bits of information from that of the away team and the home team. You will see how I separate the games by the light gray shading. According to the date on the left, I want the areas highlighted in yellow to be done automatically. Right now, I am having to go to each teams tab and find the team line and the OPR. And that is it.

Look at the formulas in cells G55 and I55. Note, the date format and the team name were modified for simplicity sake. Again, you want unified team names and, for your own convenience, make those names shorter. For instance, I would use CLE instead of Cleveland.

Hope this helps.

[robfl22]

Data,

Wow, thank you so much! I could never get by the whole DATE thing. Well, now I got a little bit of work to do to make all those team sheet tabs uniform. I will let ya know when Im done in case you have any other suggestions. Thanks so much

[robfl22]

I did everything you said...I guess im just not that good with formulas. Its alot better than it was but it is still far from automatic with the spread part of it. The totals part of it is done and its incredible. Anyways..still need more help if anyone is interested.

[Data]

Just be specific as you were in the post #5 in this thread and, likely, we can figure it out.

[robfl22]

Thanks Data..I will try. I think the one single most easiest thing I can do is not try to make this workbook as pretty I can. So, KISS theory from now on is going to apply. Dotn know if you kept the spread sheet or not that was posted before but I am going to try and get all my calculations in the ALL sheet tab. Reason being is that the schedule is already there and I can easily sort all the data to get the info I need. It will also eliminate a ton of typing. The other problem I noticed is that I am not getting the right values back from the deviation table page and I have been told the reason for this is because I am dealing with averages in alot of my calculations. Even if I make all the numbers whole with no decimal points, excel still reads right thru that apparently. I have a new idea as to how go about fixing this thing. Give me another day or two and I will post the updated sheet. Oh, and one more problem..if the home team is favored in the game, the line reads as a plus line and not negative...and vice versa for the away favorite. But that should be easy to figure out.

[Data]

robfl22, it seems you are making progress and I am glad if I was able to help. Just a note of caution, keep doing this only if you actually like doing this kind of work. It is very time consuming, the road is long and you just started. Do not expect to finish the project in a week or few and then reap the rewards.

[robfl22]

data,

I need your help again. On the very first worksheet MATCHUPPICKS, I am now getting alot of NA's. I dont know why this is cause I checked everything I knew.Plus, all I did was copy down from the previous formulas that were working. Can you help me out with this? I tried to upload the spreadsheet onto here again so you could review it but the site wouldnt let me cause it said I had a security code missing. Any other way I can get this updated spreadsheet to you? Also, I got the totals the way I want them now on the ALL sheet tab. Think were just a few steps away from nailing this. Thanks

[Data]

robfl22, the problem was mainly due to home/away mix up.