Last six league goals calculation

**embwabili** · 03-14-2010, 10:56 AM

Dear all,
In the file attached, is a sheet showing an excerpt of football league. Could you guys help in showing how I can fill out columns G, H, I, J? Remember, at one point a team is a home and another point the team is away.
your kind help will be highly appreciated
Bulls

**shg** · 03-14-2010, 12:32 PM

Welcome to the forum, embwabili.

Both this and your other post look a lot like homework problems. We don't do those, but will help if you have a specific question other than, "How do I do this?"

**embwabili** · 03-15-2010, 01:54 AM

Hi, shg, sharp eye, connecting the 2 posts :-)
But actually they are not homework problems, am simply trying to develop my own custom system to beat the bookies. Want to make more informed decisions before I place my next bet. Can you now help?

**Leith Ross** · 03-15-2010, 02:20 PM

Hello embwabili,

Welcome to the Forum!

The attached workbook has a button to run the macro. When the macro runs it will fill in the worksheet columns "G:J" with the scores of the 6 most recent games. All the data is first sorted by date in descending order and then alphabetically by team. Here is the macro that has been added...

Please Login or Register  to view this content.

**embwabili** · 03-16-2010, 04:00 AM

Dear Leith,
Thanks so very much for the help. Unfortunately, when I run the macro and manually crosscheck the values/results, I see that it is not returning correct values. I have tried to see if there is an offset or particular trend in the inconsistency but there seems to be no correlation.
Kindly re-crosscheck.
Thanks once again

Regards
Bulls

**DonkeyOte** · 03-16-2010, 04:55 AM

Needless to say it generally helps if you provide expected results - this way everyone can validate their logic (eg "current" game included in the last n results ?)

I appreciate you have (seemingly) requested code based solution given Forum in which this is located but just to illustrate that you can (if so desired) resolve with formulae see attached.

On very large data sets it would make sense to use more helpers to negate need for Arrays / SUMPRODUCT etc... a UDF / Sub Routine is not a bad idea

To reiterate - I'm not advocating this above and beyond a VBA based solution - merely an alternative.

EDIT: upload removed and replaced by version in latter post (#10)

**embwabili** · 03-16-2010, 06:03 AM

Dear DonkeyOte,
Having posted to this forum was in belief that I would get help here better than in any other place, not because I preferred a particular method of solution over any other. What works first is what I go with and later I can consider what works elegantly. Feel free to suggest what works.
How to verify logic?
col G is filled by summing all the goals scored by a home team(col b) in last six previous appearances before the current fixture. The six appearances are counted regardless of whether that team featured at home or away. What matters are 6 appearances before the row in question. So the summation has to pick value from either col D or E for those 6 appearances depending on whether the team was away or at home.
col H is filled by summing all the goals that the home team conceded in fixtures considered above.
col I is filled by using the same logic like for G but this time considering the away team(col C).
col J is filled by using the same logic like for H but this time considering the away team(col C).
The current fixture is not counted among the 6, only the previous ones.
I hope it is more clear now.
DonkeyOte, are the formulae you put in the sheet examples or they are the ones I should use in verification?
Thanks alot once again
Regards
Bulls

**DonkeyOte** · 03-16-2010, 06:13 AM

embwabili, I think you misunderstood my point... you should provide expected results in your sample file at least for a few cells.
Not doing so means people are guessing based on interpretation - they having nothing empirical against which to measure their own results.

Originally Posted by embwabili

DonkeyOte, are the formulae you put in the sheet examples or they are the ones I should use in verification?

The prior attachment was pretty self explanatory I think, however, given the below:

Originally Posted by embwabili

summing all the goals scored by a home team(col b) in last six previous appearances before the current fixture

they will need to be "tweaked" given I had incorrectly presumed "current" result to be inclusive as opposed to exclusive (hence earlier point regards providing expected results).

To tweak you must change the 5 to a 6 in the SMALL arrays in L2 & M2 - remembering to reset the Array (CTRL + SHIFT + ENTER) - then copy down over remaining rows.
Adjust G:J by removing reference to current score if not to be included (ie remove $D2+ etc...)

**embwabili** · 03-16-2010, 07:20 AM

Dear DonkeyOte,
You brought a smile to my face. The formulas are working OK with the current match included, thanks. Following your instructions, I'll do the tweaking to remove the current match.
There's one issue though, the calculations are coming bottom up i.e starting with the newer matches instead of the older ones. Could you help change them to start from the top going downwards i.e start with the older matches and calculations going on downwards as the league progresses?
Thanks alot DonkeyOte

Very warm regards
Bulls