avg the last four entries

[thefrozendog]

Hey guys can someone help me out. I have a general knowledge and can do most excel stuff with basic formulas but Im kind of lost on this. Heres the deal. I have a football ranking system and I enter the scores each week and it calulates the teams power. I have to make several manual changes each week and Im looking to change this. So heres the problem. Baylor plays 13 games. and I have 15 columns to record scores (since some teams play more games). F3:T3 is the range for baylors scores. I base my rankings off the last four games. So I need a formula that will average the last four games and then switch itself when I enter then most recent game so that it is included with the other three most recent games. Also in some cells I will have blanks, B (by week), and AA (div1aa) all of these need to be ignored. I need it to start at the most recent game (on the right most score) and go back four SCORES not cells and avg those 4 SCORES. Hope I made this somewhat coherent. Thanks in advance for any help.

 

[thefrozendog]

Oh man that one worked. THANKS A MILLION MAN! You have know idea how much work you just saved me. 117 teams and 13 or 14 games a year. Whew! Time for sleep =)

 

[thefrozendog]

Thanks agian howzat for your last formula it will save me tons of work.

howzat or anyone who can answer I have one more question. I need a formula based on the same cells F3:T3 that will add up the last four games and give me a number of wins. SO what I need it to do is look in the last four games like the previous formula and then for each of those four look in the cell directly below (opponents scores) and if its greater than the cell below add 1 to the sum. In this case P3 looks into P4 and if P3 is greater than P4 it would be 1 and if Baylor has won all four games it will show 4 in the cell(B3). Thanks guys for any help.

 

[howzat]

Is each team's opponent always in the row directly below it, or could they be all over the show? I ask this since there are 117 teams etc...

 

[thefrozendog]

Correct each teams opponent is always directly below. That way all I have to do is add the team score hit enter and add the opponenet score hit enter add the next team score etc.

 

[howzat]

Try the following:

=SUMPRODUCT((INDEX(3:3,LARGE(COLUMN(F3:T3)*(F3:T3<>""),MIN(4,COUNTA(F3:T3)))):T3>INDEX(4:4,LARGE(COLUMN(F4:T4)*(F4:T4<>""),MIN(4,COUNTA(F4:T4)))):T4)*1)

A point is obtained only if a team wins. It will not tell you how many point the bottom team has. You will have to swap the formula around as follows:

=SUMPRODUCT((INDEX(4:4,LARGE(COLUMN(F4:T4)*(F4:T4<>""),MIN(4,COUNTA(F4:T4)))):T4>INDEX(3:3,LARGE(COLUMN(F3:T3)*(F3:T3<>""),MIN(4,COUNTA(F3:T3)))):T3)*1)

 

[thefrozendog]

It works BEAUTIFULLY! The seperate cells for win and loss is how I had it set up but now I dont have to manually change each week. You are Awesome man. Thanks!

 

[tberry]

I'm trying to do something similar with golf scores. I coach and would like to have not only my player's average, but also a "rolling" average of their last 4 scores. My scores (and many other stats) are on various worksheets in the excel spreadsheet. The scores (and some players won't have some during some matches) are located on the following:

Match1!O24+Match2!O24+Match3!O24+Match4!O24+Match5!O24+Match6!O24+Match7!O24+Match8!O24+Match9!O24+Match10!O24+Match11!O24+Match12!O24+Match13!O24+Match14!O24+Match15!O24+Match16!O24+Match17!O24+Match18!O24+Match19!O24+Match20!O24+Match21!O24+Match22!O24


Thanks for the help!