"Average If Not Blank" Formula

[CindyA]

I have 10 columns and 5 rows of cells. All cells do not
contain a number and the cells which DO contain numbers
are sometimes 1, 2, or 3 digits. I need to enter a
formula to the right of the grid of cells to average the 2
most right column's cells. For instance, in row 1, if
cells A1, A2, and A3 contained numbers 2, 33, 40
respectively, I need to have the formula skip looking at
cells A4 through A10 and average the cells containing
numbers. Any ideas?

 

[Don Guillett]

Have you trie the AVERAGE function?

 

[CindyA]

Yes, but I only need the LAST 2 right-most cells in the
range of 10 cells.

 

[CindyA]

P.S. In addition, the cells which contain numbers may be
different on every row. Thanks.

 

[Frank abel]

Hi
do you want the average for a column or for a row. Looking
at your other post I assume you want the average per
column (e.g. from A1:J1) BUT also considering to take only
the last to filled cells.

I assume that you don't have blank cells in between per
row (e.g. if D1 is filled A1:C1 is filled also)

Try the following formula in cell K1
=AVERAGE(OFFSET($A1,COUNTA($A1:$J1)-1,1,-2))
and copy this down

 

[Charlie]

Cindy,

The average function will only calculate cells that
contain data. In your example A1, A2, and A3 contained
numbers 2, 33, 40. If you use =AVERAGE(A1:E10) the return
will be 25. If the cells contain a "0" Zero the "0" is
considered in the calculation. Cells that are truly empty
will not figure in the calculation.

If this is not what you are looking for perhaps you could
post some sample data.

Charlie O'Neill

 

[CindyA]

Here is a sample. Thanks for taking the time to look at
this.

A1=10
B1=8
C1=22
D1=4
E1=55
Cells F1 through J1 are blank.
In cell K1 I want the formula to look left, determine
which are the last TWO cells to contain numbers and
average just those two cells. So in this example, only
Cells D1 and E1 would be considered. Cells A1, B1, C1, as
well as F1 through J1 would be ignored.

 

[CindyA]

Works great if all cells are filled in, but unfortunately,
I periodically will have a blank cell in the mix. Any
solution to this?

Thanks in advance.

 

[Ron Rosenfeld]

Here is a sample. Thanks for taking the time to look at
this.

A1=10
B1=8
C1=22
D1=4
E1=55
Cells F1 through J1 are blank.
In cell K1 I want the formula to look left, determine
which are the last TWO cells to contain numbers and
average just those two cells. So in this example, only
Cells D1 and E1 would be considered. Cells A1, B1, C1, as
well as F1 through J1 would be ignored.

This *array-entered* formula will do what you want, I think:

=AVERAGE(TRANSPOSE(OFFSET(A1,0,LARGE(ISNUMBER(A1:J1)*COLUMN(A1:J1),{1,2})-1)))

To *array-enter* a formula, after typing or pasting the formula, hold down
<ctrl><shift> while hitting <enter>. Excel will place braces {...} around the
formula if you did this correctly.

You can array-enter the formula in K1, and then drag it down as many rows as
you have.


--ron

 

[Frank Kabel]

Hi
sure
try the following array formula (entered with CTRL+SHIFT+ENTER
=AVERAGE(OFFSET($J1,0,0,1,-(COLUMN($J1)-LARGE(IF($A1:$J1<>"",COLUMN($A1
:$J1)),2)+1)))

 

[CindyA]

Thanks, I'll give it a try.

-----Original Message-----
Hi
sure
try the following array formula (entered with CTRL+SHIFT+ENTER
=AVERAGE(OFFSET($J1,0,0,1,-(COLUMN($J1)-LARGE(IF

 

[CindyA]

Thanks, I'll give it a try.

-----Original Message-----


This *array-entered* formula will do what you want, I think:
*COLUMN(A1:J1),{1,2})-1)))

To *array-enter* a formula, after typing or pasting the formula, hold down
<ctrl><shift> while hitting <enter>. Excel will place braces {...} around the
formula if you did this correctly.

You can array-enter the formula in K1, and then drag it down as many rows as
you have.


--ron
.