Need to Ignore Alpha in Array (using to take average of last 5 entries)

[bhall1183]

I'm trying to update an existing formula/array that has been working well to accommodate an additional request. Right now, this formula takes the average last five entries in a row from the leftmost entry. I need to update this to ignore alphas (specifically, an "x") from impacting the average and the count of last five entries.

Current formula:
{=SUM(IF(COLUMN(R3:CC3)>=LARGE(IF(R3:CC3<>"",COLUMN(R3:CC3)),5),R3:CC3))/5}
*works well w/out alphas, but w/alphas, it counts it as an entry of 0

Attempt #1 :
{=SUM(IF(ISNUMBER(COLUMN(R3:CC3))>=LARGE(IF(R3:CC3<>"",COLUMN(R3:CC3)),5),R3:CC3))/5}
*ignores alphas, but no longer averages correctly -
Example - 10 entries of 1, should average to 1, but it calculates it to 2
[TABLE="width: 330"]
<tbody>[TR]
[TD="class: xl64, width: 33"]1[/TD]
[TD="class: xl63, width: 33"]1[/TD]
[TD="class: xl63, width: 33"]1[/TD]
[TD="class: xl63, width: 33"]1[/TD]
[TD="class: xl64, width: 33"]1[/TD]
[TD="class: xl64, width: 33"]1[/TD]
[TD="class: xl64, width: 33"]1[/TD]
[TD="class: xl63, width: 33"]1[/TD]
[TD="class: xl63, width: 33"]1[/TD]
[TD="class: xl63, width: 33"]1[/TD]
[/TR]
</tbody>[/TABLE]
This should only look at the last five, but I'm getting a value of 2

[TABLE="width: 330"]
<tbody>[TR]
[TD="class: xl66, width: 33"][TABLE="width: 330"]
<tbody>[TR]
[TD="class: xl66, width: 33"]1[/TD]
[TD="class: xl65, width: 33"]1[/TD]
[TD="class: xl65, width: 33"]1[/TD]
[TD="class: xl65, width: 33"]x[/TD]
[TD="class: xl66, width: 33"]1[/TD]
[TD="class: xl66, width: 33"]1[/TD]
[TD="class: xl66, width: 33"]x[/TD]
[TD="class: xl65, width: 33"]1[/TD]
[TD="class: xl65, width: 33"]1[/TD]
[TD="class: xl65, width: 33"]1[/TD]
[/TR]
</tbody>[/TABLE]
This should be 1, ignoring the 2nd "x", but I get 1.6[/TD]
[TD="class: xl65, width: 33"][/TD]
[TD="class: xl65, width: 33"][/TD]
[TD="class: xl65, width: 33"][/TD]
[TD="class: xl66, width: 33"][/TD]
[TD="class: xl66, width: 33"][/TD]
[TD="class: xl66, width: 33"][/TD]
[TD="class: xl65, width: 33"][/TD]
[TD="class: xl65, width: 33"][/TD]
[TD="class: xl65, width: 33"][/TD]
[/TR]
</tbody>[/TABLE]

Any thoughts? Thanks!

 

[Domenic]

Try...

=SUM(IF(ISNUMBER(A2:J2),IF(COLUMN(A2:J2)>=LARGE(IF(ISNUMBER(A2:J2),COLUMN(A2:J2)),5),A2:J2)))/5

...confirmed with CONTROL+SHIFT+ENTER. Adjust the ranges accordingly.

Hope this helps!

 

[bhall1183]

That worked perfectly! Thank you Domenic!

 

[Domenic]

You're very welcome!