"Average If Not Blank" Formula
Thanks, I'll give it a try.
-----Original Message-----
On Tue, 4 May 2004 08:17:59 -0700, "CindyA"
wrote:
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:J 1)
*COLUMN(A1:J1),{1,2})-1)))
To *array-enter* a formula, after typing or pasting the
formula, hold down
ctrlshift 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
.
|