Excel formula for first and second half NBA O/U help

[bizzo1]

I have started recording a new betting trend idea on the NBA first and second half O/U scores. I need some help with getting some info to auto calculate in excel. If you have a look at the spreadsheet you can see I have separated out the home team in column A and the away team in column B and I have just been keeping record manually of what all of the scores have been against the lines over the past few weeks. The second half Under has been staying consistently over 60% in the win column. I started noticing a trend of teams that were going over in the second half so I am looking to keep track of each team and weather they go over or under in both the first and the second half. If you have a look at the bottom of the spreadsheet you will see I have listed every NBA team and I have cells created to record how many times a team goes over or under the total in both the first and second half of games. Could someone help me formulate these cells so that when I put new games in each day it will look for each team and add up the over under column automatically? Here is the link to my spreadsheet https://spreadsheets0.google.com/ccc...COud480C#gid=0

Any help would be appreciated.

[djiddish98]

I don't know if Googledocs supports it, but I imagine a =COUNTIF or =SUMIF function would work

[bizzo1]

I know I had the Countif function working to count how many times "CHI" showed up in column A but I need to add to that formula so that it will add up how many times that team goes over and under in both halves and then register that number at the bottom.

[jane2geo]

Don’t have time now, but I’m positive your answer will be a VLOOKUP. Check-out the Solver add-in tutorial from MS. Does a pretty good job explaining. =VLOOKUP(F38,lookup,4)+VLOOKUP(F38,looku p,9) Where “lookup” is a range of cells in this case, probably your entire sheet.

[splash]

Pretty sure google docs will not support =Countifs because excel 2003 doesn't even support it. But that is what you are looking for. Excel 2007 will do it. It's countif with multiple criteria.

[Insoluble]

=Countif would be a good solution but if that is not available on Googledocs I would suggest building an if statement to do your dirtywork. You could have it look for the team name and "if" that exists then it could add whatever is in the over or under column for that particular game to your "sum" columns

[jane2geo]

Place this formula in cell B237 =SUMIF(A2:A228,"BOS",E2:E228)+SUMIF(B2:B 228,"BOS",E2:E228)

[EXhoosier10]

Place this formula in cell B237 =SUMIF(A2:A228,"BOS",E2:E228)+SUMIF(B2:B 228,"BOS",E2:E228)

This looks to be what you're looking for. Good work jane

[bizzo1]

Sweet Jane that worked! You rock

[flamethrower]

Thank you just what I was looking for to get a better understanding. I was completely blind on how to go at this before.

[jane2geo]

I believe this was my first positive contribution to the forum. Thanks for the kind words.

[jane2geo]

Hi Jane2geo,

Just noticed you inputted a nice formula to help out a player with excel. How do you know all this stuff??? Any advice you can give me to help me learn the ropes. I'm just starting out with my models, but figuring out formulas like these would definitely aid in the process, or at least knowing where to begin. Thanks.

I manage to get by on a need to know basis. Basically, if you know what you want someone else has already been there and done that. Excel has hundreds of functions, add-ins and many smart people have even created custom functions like “Stock Market Functions Add-In”. Don’t forget Goggle is your friend.

You only need one question to get started. When you find the answer, by actually starting somewhere, you may come across another question. The questions lead the way, and as you find the answers you become more empowered and certain in your ways. The title of this thread is not hopeful, where it comes to your ability to think for yourself.

[R23k]

That seems like a lot of work

[fminc]

I have started recording a new betting trend idea on the NBA first and second half O/U scores. Here is the link to my spreadsheet https://spreadsheets0.google.com/ccc...COud480C#gid=0

Thanks for this!