"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?
.
.
.
|