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

#### mdean32

##### New Member
 A B C D E F G H I J 30 41 37 69 46 32 40 33

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

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

#### mdean32

##### New Member
Correction, row A1:YA1, sorry

#### James006

##### Well-known Member
Hi,

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

You can test following Array Formula :

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

Hope this will help

#### mdean32

##### New Member
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

##### Well-known Member
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

Last edited:

Replies
1
Views
1K
Replies
2
Views
574
Replies
3
Views
494
Replies
0
Views
208
Replies
0
Views
530

1,109,423
Messages
5,528,681
Members
409,829
Latest member
CFreeamaz

### This Week's Hot Topics

• Change military grades into rank
Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
• VBA COUNTIF SOLUTION
Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
• INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...