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

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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!
 
Upvote 0
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 :)
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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<>"")
 
Upvote 0
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.
 
Upvote 0
hey where did that post from the yogi guy go? Yogi I tried it but its averaging all the numbers in F3 through T3.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

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
Back
Top