# avg the last four entries

#### thefrozendog

##### New Member
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

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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!

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

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.

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

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

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<>"")

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.

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

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))".

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

Replies
2
Views
145
Replies
1
Views
167
Replies
1
Views
251
Replies
5
Views
296
Replies
9
Views
1K

1,221,237
Messages
6,158,714
Members
451,510
Latest member
kegnazmach

### 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.

### Which adblocker are you using?

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

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