Formula to average last 3 values in a row, while ignoring blank cells?

[mdean32]

[TABLE="width: 500"]
[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]41[/TD]
[TD]37[/TD]
[TD]69[/TD]
[TD]46[/TD]
[TD][/TD]
[TD]32[/TD]
[TD][/TD]
[TD]40[/TD]
[TD]33[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[/TABLE]

What formula could I use to average the last 3 values of row A1 through J1 while ignoring the blank cells? This would have new data entered daily, so the row with be ongoing, let's say A1:YA4. As new data is entered, I always want the formula to calculate the last 3 values entered. I appreciate any help you might give

 

[mdean32]

Correction, row A1:YA1, sorry

 

[James006]

Hi,

In order to get the average of the last 3 columns with a numeric entry ...

You can test following Array Formula :

=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(A1:YA1),COLUMN(A1:YA1)),{1,2,3}), COLUMN(A1:YA1), A1:YA1))

Hope this will help

 

[mdean32]

Thank you, and this does help, however if I extend the range to A1:YA1, it gives me a zero. It will only calculate the last 3 values if I have my range A1last cell with data). In my above example it would be A1:J1. Is there a work around for this? Example, K1 through YA1 cells may be blank at the time, but over time will have some data. Thanks again

 

[James006]

Hi again,

Not sure to understand your constraints .... should you go beyond column YA ...

To handle all potential situations ... you could use A1:XFD1

HTH