Poisson with large data sets - HELP!!

[stufferonald]

Hi Guys,

I am currently working out (or at least trying to work out) a way to forecast NBA scores. It's a bit of a hobby and I wanted to see if I could create something with a decent level of accuracy. I have done it for soccer already, but I calculated this manually (as the data range was only 0-10). In NBA, the range is say 70-140, so manually creating the formulas to determine home/away wins would take an eternity!

Is there any way you know of that could automated this process. i.e. work out a % chance of Team A outscoring Team B? As I mentioned earlier, the soccer one worked fine manually, I populated a grid of all possible score lines and the respective chance of each. The NBA grid would be huge, so if there was a way to do it with a Pivot table or something that would be great! Open to ANY suggestions!

At the minute, my spreadsheet creates a % chance for the home and away side scores from 70-140. Last night for example it suggested Dallas Mav's would score 106 to the Memphis Grizzlies 94 (actual score was 105-91 - so not a million miles away ) - I had to go through my huge grid manually, but could do with being able to extract that information automatically

If any of this doesn't make sense, let me know - but hopefully someone out there can help me out

Thanks

Stuffer

[MrShorty]

Without the details of the process, I don't think any of us on here will be able to help you very much. We are pretty good at programming an algorithm into Excel once we understand the details of the algorithm. Outside of our own individual spheres of expertise, we are not necessarily good at developing the algorithm.

Here's how I would probably approach the problem. You say that you know how to "manually" solve the problem in a soccer spreadsheet, where the matrix is much smaller. I would probably take that soccer spreadsheet and carefully think through every "manual" step you take to get the result. The goal will be to figure out a cell formula (or multiple cell formula if each manual step can be further broken down into smaller steps) that will perform each manual step for you. Then, you can combine the results of the manual steps into a calculation for the final result. The advantage in developing the spreadsheet on the smaller soccer spreadsheet is that, for each step, you will know what result it should give, which will give you a solid basis for debugging and testing. Once you have it working on the smaller soccer spreadsheet, it should be relatively easy to expand the algorithm to the larger NBA spreadsheet.