# Simple problem, difficult solution II

#### Pericic

##### Board Regular
CDEFGHIJKLMN
7TABLE A: Schedule - ScoresTABLE B: Database
8WeeekTeam AScoreTeam BScoreTeamPFPAGamesAvg PFAvg PA
91NewEngland21Denver24NewEngland2124121.024.0
10Indianapolis24Atlanta21Indianapolis2421124.021.0
14PittsburghNewEnglandDenver2421124.021.0
15
16TABLE C: Predictions
17WeeekTeam Ap/sTeam Bp/s
182Denver22.5Indianapolis22.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

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

#### Russ At Index

##### Well-known Member
Hi Pericic

Just one question regarding how you "assume/calculate"

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:
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.

#### Pericic

##### Board Regular
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

#### PA HS Teacher

##### Well-known Member
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
62Denver56Indianapolis21
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
7Atlanta_11Atlanta212412124
8Denver_11Denver242112421
9NewEngland_22NewEngland386921934.5
10Indianapolis_22Indianapolis4577222.538.5
11Pittsburgh_22Pittsburgh723823619
Data Base

#### PA HS Teacher

##### Well-known Member
The Predictions Sheet:
Football Wb 11-12-05.xls
ABCDE
1TABLE C: Predictions
2WeekTeam Ap/sTeam Bp/s
32Denver22.5Indianapolis22.5
52Pittsburgh25.5NewEngland21
63Denver39.25Indianapolis21.75
83Pittsburgh35.25NewEngland19
Predictions

#### Pericic

##### Board Regular
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...??

Thank you very much

#### PA HS Teacher

##### Well-known Member
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.

#### Pericic

##### Board Regular
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

#### PA HS Teacher

##### Well-known Member
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``````

#### Pericic

##### Board Regular
Thank You Russ soooo much

Replies
0
Views
123
Replies
3
Views
300
Replies
5
Views
212
Replies
3
Views
226
Replies
1
Views
124

1,196,014
Messages
6,012,859
Members
441,736
Latest member
Tkpmm

### 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.

### Which adblocker are you using?

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

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