Average Last x cells excluding Blanks Cells and Text

[Grunter31]

I have a massive stats based excel spreadsheet that new data gets imported daily. I need to get the average of the last x games ("x" so I can select the number of games to average).

What I thought was going to be a basic average(offset) formula has turned into a nightmare.

The problem that I have is that the imported Data has Blank Cells and some Cells that contain Text.

Basic Example: Column of Data has 3 blank cells and starts in Row 4 and Continues down for over 100 cells and new data gets imported to the bottom.
[TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl63, width: 64"][TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl65, width: 64"]A1[/TD]
[TD="class: xl66, width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl65"]A2[/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl65"]A3[/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl68"]A4[/TD]
[TD="class: xl66"]PTS[/TD]
[/TR]
[TR]
[TD="class: xl65"]A5[/TD]
[TD="class: xl66"]28[/TD]
[/TR]
[TR]
[TD="class: xl65"]A6[/TD]
[TD="class: xl66"]17[/TD]
[/TR]
[TR]
[TD="class: xl65"]A7[/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl65"]A8[/TD]
[TD="class: xl66"]PTS[/TD]
[/TR]
[TR]
[TD="class: xl65"]A9[/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl65"]A10[/TD]
[TD="class: xl66"]23[/TD]
[/TR]
[TR]
[TD="class: xl65"]A11[/TD]
[TD="class: xl66"]PTS[/TD]
[/TR]
[TR]
[TD="class: xl65"]A12[/TD]
[TD="class: xl66"]16[/TD]
[/TR]
[TR]
[TD="class: xl65"]A13[/TD]
[TD="class: xl66"]6[/TD]
[/TR]
[TR]
[TD="class: xl65"]A14[/TD]
[TD="class: xl66"]PTS[/TD]
[/TR]
[TR]
[TD="class: xl65"]A15[/TD]
[TD="class: xl66"]21[/TD]
[/TR]
[TR]
[TD="class: xl65"]A16[/TD]
[TD="class: xl66"]22[/TD]
[/TR]
[TR]
[TD="class: xl65"]A17[/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl65"]A18[/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl65"]A19[/TD]
[TD="class: xl66"]18[/TD]
[/TR]
[TR]
[TD="class: xl65"]A20[/TD]
[TD="class: xl66"]11[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="class: xl64, width: 64"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Let column A of Sheet1 house the data.

Define Data in Formulas | Name Manager as referring to:

=Sheet1!$A$1:INDEX(Sheet1!$A:$A,MATCH(9.99999999999999E+307,Sheet1!$A:$A))

Let D1 house the specification for "last x games", i.e. the value x.

In D2 control+shift+enter, not just enter:

=AVERAGE(IF(ROW(Data)>=LARGE(IF(ISNUMBER(Data),ROW(Data)),MIN(D1,SUM(IF(ISNUMBER(Data),1)))),IF(ISNUMBER(Data),Data)))

 

[Grunter31]

That works brilliantly.

I have a question regarding array formulas {..} I have to repeat this formula maybe 1000 times (which isn't the problem as I'm a master of the copy and paste lol) my question is will this slow my spreadsheet down to a stand still. If so do you have any solutions to help with this.

 

[Aladin Akyurek]

That works brilliantly.

I have a question regarding array formulas {..} I have to repeat this formula maybe 1000 times (which isn't the problem as I'm a master of the copy and paste lol) my question is will this slow my spreadsheet down to a stand still. If so do you have any solutions to help with this.

That many times, I don't think so. Let's take it up if it doesn't measure up performancewise.

 

[Grunter31]

I'd just like to pass on a massive thank you.

A few hours putting your formula in the right places will save hours of work down the track.

I will say the Find and Replace function on excel is very underrated.

 

[Aladin Akyurek]

You are welcome. Thanks for the update.