Formula to work out average value from a range of cells containing some blanks

[Jonny_71]

Hi,

I need to know a formula which will work out an average based on the following:

- Row A represents game weeks (e.g. week 1, week 2, etc.)
- Week 6 is the most recent week, week 5 is the next most recent week, etc.
- Row B represents points scored in each game week, e.g. week 6 = 5 points, week 5 = 0 points, etc.
- The blank values (e.g. week 4 and week 2) there was no game

I need the formula to calculate the average points scored in the three most recent weeks where there has been a game, e.g. the formula would need to calculate the average of weeks 6, 5 and 3 in the example below, i.e. (5 + 0 + 1)/3 = 2 points.


[TABLE="width: 258"]
<colgroup><col span="6"></colgroup><tbody>[TR]
[TD]6[/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD]1[/TD]
[TD] [/TD]
[TD]8[/TD]
[/TR]
</tbody>[/TABLE]

I'm struggling with this. Many thanks for your help.

 

[Eric W]

Welcome to the forum.

Maybe:

=AVERAGE(OFFSET(A2,0,0,1,SMALL(IF(A2:F2<>"",COLUMN(A2:F2)),3)))
confirmed with Control+Shift+Enter.

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=AVERAGE(IF(COLUMN(A2:F2)<=SMALL(IF(ISNUMBER(A2:F2),COLUMN(A2:F2)),MIN(3,COUNT(A2:F2))),IF(ISNUMBER(A2:F2),A2:F2)))

 

[Jonny_71]

Thank Eric. I've tried the formula suggested above but it doesn't seem to work. It returns the answer 3.5 when the answer should be 2.

I think the formula is calculating the average of all cells where a value is entered (i.e. 5+0+1+8) then dividing it by the number of cells with values (i.e. 4) to give the answer 3.5.

However I only want the formula to calculate the average for the three most recent values (i.e. 5+0+1 divided by 3) to return an answer of 2.

Any idea how I can do this?

Thanks.

 

[Jonny_71]

Control+shift+enter, not just enter:

=AVERAGE(IF(COLUMN(A2:F2)<=SMALL(IF(ISNUMBER(A2:F2),COLUMN(A2:F2)),MIN(3,COUNT(A2:F2))),IF(ISNUMBER(A2:F2),A2:F2)))

That seems to work, thank you very much.

 

[MARZIOTULLIO]

wrong formula

 

[Aladin Akyurek]

That seems to work, thank you very much.

Glad to help.