Most recent values. Help plz.

[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 guys in advance for any help.

 

[Biff]

What up frozendog!

When you say you record the score, do you mean in one cell
and in the traditional format,like 33-10? If so, you're
asking for miracles!!!!

If on the other hand, you just enter the score as a single
number like 33, then we can work with that.

Try this formula:

=AVERAGE(IF(ISNUMBER(F3:T3)*(COLUMN(F3:T3)>=LARGE(IF
(ISNUMBER(F3:T3),COLUMN(F3:T3),""),4)),F3:T3,""))

Entered as an array - CTRL+SHIFT+ENTER

This will work *if* there are at least 4 scores to
average. If there are less than 4, it will error out.

Biff

 

[Jazzer]

Hi,

Put this array formula on the same row as the teams scores, that you
are calculating (for example in cell B3 if the scores are in F3:T3).

=AVERAGE(OFFSET(INDIRECT(ADDRESS(ROW(),LARGE((IF(ISNUMBER(F3:T3),COLUMN(F3:T3),6)),4))),,,1,MAX((IF(ISNUMBER(F3:T3),COLUMN(F3:T3),6)),4)-LARGE((IF(ISNUMBER(F3:T3),COLUMN(F3:T3),6)),4)+1))

Because it's an array formula, you should enter it by pressing shift
+ ctrl + enter, not just enter. To indicate, that it is entered as an
array formula, there are { } characters around it after it is entered.

When there are no values in the scores area (F:T), it returns
#DIV/0!, but on the other cases it should work just fine.

- Asser

 

[thefrozendog]

Thanks for the help