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.

A1 A2 A3 A4 PTS A5 28 A6 17 A7 A8 PTS A9 A10 23 A11 PTS A12 16 A13 6 A14 PTS A15 21 A16 22 A17 A18 A19 18 A20 11 <tbody> </tbody>

<tbody>
</tbody>

 

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