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.

 

[Brian from Maui]

Try,

=SUM(INDEX(2:2,,LARGE(COLUMN(C2:IV2)*(C2:IV2>0),$C$1)):IV2)/$C$1

where 2:2 is the row number and $c$1 houses 4

and should be arrayed entered!

 

[Zack Barresse]

and also, the average function should, by all rights (which means for me ) will ignor blanks (completely empty) cells within it's specified range.

just to keep in mind

 

[thefrozendog]

Dang that was fast. Umm Im not sure I know what that formula is doing so Im not sure what to adjust for my specific cells. And yes I know blanks arent usually counted but just wanted to make sure that those and and aa wouldnt be counted in what ever you guys came up with.

Could you rewrite that formula so that it looks in F3 through T3 and I can place the formula and answer in U3.


Thanks again.

 

[Brian from Maui]

Dang that was fast. Umm Im not sure I know what that formula is doing so Im not sure what to adjust for my specific cells. And yes I know blanks arent usually counted but just wanted to make sure that those and and aa wouldnt be counted in what ever you guys came up with.

Could you rewrite that formula so that it looks in F3 through T3 and I can place the formula and answer in U3.


Thanks again.

In U3 enter

=SUM(INDEX(3:3,LARGE(COLUMN(F3:T3)*(F3:T3>0),$C$1)):T3)/$C$1)

enter 4 or whatever number you want to average by in C1

enter pressing CTRL+SHIFT+ENTER

It should average the last 4 scores in the row not counting 0's

 

[thefrozendog]

hmm didnt work. It gave me #value! and I entered by pressing CTRL+SHIFT+ENTER

 

[howzat]

Excellent formula. One more thing. If the team scores "0" points in a game, and you want that to be included in the avarage, substitute (F3:T3>0) with (F3:T3<>"")

 

[thefrozendog]

Oh I see you want me the have the divisor in another cell. Is there a way to do it like the average function where it does it for you without that seperate cell?




thanks howza I do need it that way.

 

[thefrozendog]

hey where did that post from the yogi guy go? Yogi I tried it but its averaging all the numbers in F3 through T3.

 

[howzat]

You could just replace $C$1 with "4". Furthermore, if you have less than than 4 scores which you wish to average, substitute $C$1 with "MIN(4,COUNTA(F3:T3))".

Your formula should now read as follows:

=SUM(INDEX(3:3,LARGE(COLUMN(F3:T3)*(F3:T3<>""),MIN(4,COUNTA(F3:T3)))):T3)/MIN(4,COUNTA(F3:T3)) Ctrl+shift+Enter.