Simple problem, difficult solution II

Pericic

Board Regular
Joined
Nov 11, 2005
Messages
204
answers.xls
CDEFGHIJKLMN
7TABLE A: Schedule - ScoresTABLE B: Database
8WeeekTeam AScoreTeam BScoreTeamPFPAGamesAvg PFAvg PA
91NewEngland21Denver24NewEngland2124121.024.0
10Indianapolis24Atlanta21Indianapolis2421124.021.0
11Pittsburgh27Philadelphia21Pittsburgh2721127.021.0
122DenverIndianapolisPhiladelphia2127121.027.0
13AtlantaPhiladelphiaAtlanta2124121.024.0
14PittsburghNewEnglandDenver2421124.021.0
15
16TABLE C: Predictions
17WeeekTeam Ap/sTeam Bp/s
182Denver22.5Indianapolis22.5
19Atlanta24Philadelphia22.5
20Pittsburgh25.5NewEngland21
Sheet2



This is a much better presentation of my real problem..
Table A is input data (entered manually)
Table B uses table A's data
Table C uses table B's data..

How can I prevent Table C's data from changing (or take a snapshot of it before it changes) when I enter week 2 scores (i.e., preserve the predictions for week 2 which are based only on week 1 scores; preserve week 3 predictions - based on week 1 and 2 scores; preserve week 4 predictions - based on week 1, 2, and 3 scores, and so on.......
I need this to be done automatically (no copy-paste special- values only) because my real data has thousands of rows...

For the last 2 years I have been searching the internet for the answer...

Thank you so much guys for your efforts....
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Hi Pericic

Just one question regarding how you "assume/calculate"
your predictions.

Lets take the following :

Example 1

Denver scored 24 pts away from home in game 1
Indianapolis scored 24 pts at home in game 1
Your prediction appears to be correct : Outcome = Drawn game.

However:

Example 2

Atlanta scored 21 pts away from home in game 1
Philadelphia scored 21 pts away from home in game 1
Your prediction is that Atlanta will win at home in game 2

Outcome:
Example 1 gives no advantage / disadvantage to the home team.
In example 2 it appears that the home team are the favourites !!

Are you are happy with the data you are reporting into Table C ??.

Russ.
 
Upvote 0
Russ,
If you look at the formula for the actual prediction you will notice that I take one teams points scored and the other teams points given up (offense + defense) and take the average.....
I do this with 2 vlookup formulas
Thus: In predicting the score between denver and indianapolis:
it's Denver 22.5 (24+ 21)/2 and Indianapolis 22.5 (24+21)/2
Thanks
 
Upvote 0
The following may get very slow if you have thousands of rows, but here is one approach.

1. I put your three Tables, on 3 separate worksheets.
2. I'm assuming you've bothered to list all the matchups on the predictions sheet, and on the Games Sheet.
3. You must put the week number next to EVERY game, not just the first game that week.

The Games Sheet:
Football Wb 11-12-05.xls
ABCDE
1TABLE A: Schedule - Scores
2WeeekTeam AScoreTeam BScore
31NewEngland21Denver24
41Indianapolis24Atlanta21
51Pittsburgh27Philadelphia21
62Denver56Indianapolis21
72Atlanta35Philadelphia14
82Pittsburgh45NewEngland17
Games


The Data Base Sheet
Football Wb 11-12-05.xls
ABCDEFGH
1TABLE B: Database
2WeekTeamPFPAGamesAvg PFAvg PA
3NewEngland_11NewEngland212412124
4Indianapolis_11Indianapolis242112421
5Pittsburgh_11Pittsburgh272112721
6Philadelphia_11Philadelphia212712127
7Atlanta_11Atlanta212412124
8Denver_11Denver242112421
9NewEngland_22NewEngland386921934.5
10Indianapolis_22Indianapolis4577222.538.5
11Pittsburgh_22Pittsburgh723823619
Data Base
 
Upvote 0
Very interesting solution PA, and it does work....
In your opinion is this way better than simply having cumulative databases, i.e., having x different databases (one with week 1 scores, one with week 1 and week 2 scores, one with week 1, 2, and 3 scores and so on) and then just using vlookup with appropriate database......
This is my workaround this problem...??

I'd really like your input..
Thank you very much
 
Upvote 0
I think it is much easier to maintain a single data base with an extra field, then to have 17 databases.

If you only want to see the predictions (or Results) for a particular week, use autofilter on the week column.
 
Upvote 0
Thank you Russ...and you are right..it is very very slow...
Can you please copy and paste the Sumproduct formula for me, I want to make sure I have it right...(Cannot do it from the thread for some reason)

Once again I can't tell you how much I appreciate your help
What an awesome site this is...I'm hooked forever
 
Upvote 0
Database D3:
Code:
=SUMPRODUCT((Games!B$3:B$65535='Data Base'!C3)*Games!C$3:C$65535*(Games!A$3:A$65535<='Data Base'!B3))+
  SUMPRODUCT((Games!D$3:D$65535='Data Base'!C3)*Games!E$3:E$65535*(Games!A$3:A$65535<='Data Base'!B3))

Database E3:
Code:
=SUMPRODUCT((Games!B$3:B$65535='Data Base'!C3)*Games!E$3:E$65535*(Games!A$3:A$65535<='Data Base'!B3))+
  SUMPRODUCT((Games!D$3:D$65535='Data Base'!C3)*Games!C$3:C$65535*(Games!A$3:A$65535<='Data Base'!B3))

Database F3:
Code:
=SUMPRODUCT((Games!B$3:B$65535='Data Base'!C3)*(Games!A$3:A$65535<='Data Base'!B3))+
  SUMPRODUCT((Games!D$3:D$65535='Data Base'!C3)*(Games!A$3:A$65535<='Data Base'!B3))

Predictions C3:
Code:
=(VLOOKUP(B3&"_"&A3-1,'Data Base'!A$3:H$65535,7,0)+
VLOOKUP(D3&"_"&A3-1,'Data Base'!A$3:H$65535,8,0))/2


Predictions E3:
Code:
=(VLOOKUP(B3&"_"&A3-1,'Data Base'!A$3:H$65535,8,0)+
VLOOKUP(D3&"_"&A3-1,'Data Base'!A$3:H$65535,7,0))/2
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,937
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top