Hey guys I need some help in excel I have a problem that is driving me retarded and I'm actually at the point where I got to hold myself back from just smashing my computer.
I'll attach my worksheet so it will help with what I'm talking about.
Sheet 1 is what I'm working on
Ok I'm trying to find column I which is SOS (strength of schedule)
SOS is an average of the MOV (margin of victory) of the teams a team has played so to find Buffalo's SOS you find the average MOV of all the teams Buffalo has played.
You can do that manually by using a formula like =AVERAGE(H2,H25,H5,H4,H24,H9,H7,H6,H13,H 22,)
but I need it to it automaticaly when I add new games to the left part columns A to D and to do this you need to use the average if function something like this
=AVERAGE(IF($B$2:$D$207=G2,$B$2:$D$207))
Anyway I cant figure out the exact average if formula and its just driving me mental.
I'll attach my worksheet so it will help with what I'm talking about.
Sheet 1 is what I'm working on
Ok I'm trying to find column I which is SOS (strength of schedule)
SOS is an average of the MOV (margin of victory) of the teams a team has played so to find Buffalo's SOS you find the average MOV of all the teams Buffalo has played.
You can do that manually by using a formula like =AVERAGE(H2,H25,H5,H4,H24,H9,H7,H6,H13,H 22,)
but I need it to it automaticaly when I add new games to the left part columns A to D and to do this you need to use the average if function something like this
=AVERAGE(IF($B$2:$D$207=G2,$B$2:$D$207))
Anyway I cant figure out the exact average if formula and its just driving me mental.