"Average If Not Blank" Formula

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
($A1:$J1"",COLUMN($A1
:$J1)),2)+1)))


--
Regards
Frank Kabel
Frankfurt, Germany


CindyA wrote:
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.

-----Original Message-----
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

-----Original Message-----
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?
.

.

.