I am not sure the best way to approach this. I was thinking of doing it through functions but a VBA script may work best. Any guidence on how to accomplish these two tasks are greatly appreaciated.
I have a spreadsheet I have created to calculate rankings. I have figured out how to look up the teams wins and losses. Now I need a way to figure out how to look up all of the oponents wins and losses. The sheet is pulling all data from a score page sheet where the scores of games are entered. I now need to calculate oponents wins and losses that they have currently played.
The second peice is Strength of Schedule and Opponents Strenght of Schedule.
I would like to use current rankings as a strength adder. The last place team will be assigned a 1 counting up to the top ranked team. When a team plays a home game against the lowest ranked team, it will add a 1 (opponents strength adder) to the teams strength of schedule pool. If a team plays an away game it will be the teams I will add 1.5 times the opponents strength adder to the teams schedule strength pool. Then the team with the highest pool with have the hardest strength of schedule. as games are entered I would like the formula to change with the rankings of the teams.
Any help on this and the issue above is greatly appreciated. Attached is the spread sheet I am designing.