avg the last four entries

thefrozendog

New Member
Joined
Jan 23, 2004
Messages
42
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.
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,459
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

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
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

New Member
Joined
Jan 23, 2004
Messages
42
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

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,459

ADVERTISEMENT

thefrozendog said:
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
 

howzat

Board Regular
Joined
Oct 13, 2003
Messages
144

ADVERTISEMENT

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

New Member
Joined
Jan 23, 2004
Messages
42
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

New Member
Joined
Jan 23, 2004
Messages
42
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

Board Regular
Joined
Oct 13, 2003
Messages
144
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.
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,560
Messages
5,765,085
Members
425,258
Latest member
brentmitchell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top