"Average If Not Blank" Formula

  • Thread starter Thread starter CindyA
  • Start date Start date
C

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?
 
P.S. In addition, the cells which contain numbers may be
different on every row. Thanks.
 
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
 
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
 
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.
 
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.
 
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.
Click to expand...

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
 
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)))
 
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
Click to expand...
 
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
.
Click to expand...
 
Back
Top