AVERAGE

  • Thread starter Thread starter Bill
  • Start date Start date
B

Bill

=AVERAGE(F2,H2,J2,L2,N2,P2)

All of the above cells may or may-not have a value in
them. The formula has been placed on approximately 10k
lines of that spreadsheet (ie: Row 2 through 10k)

How can the formula be changed to exclude those cells
having no value in them?

Example:

F2 H2 J2 L2 N2 P2
3 5 9 8 7 5 (Average: Total/6)
3 2 5 9 (Avg should exclude H and N)
 
One way maybe, assuming the data starts in F2 and ends in P2, TRY using

=SUM(F2:P2)/COUNTA(F2:P2)

SUM(F2:P2) does just that,

COUNTA(F2:P2) counts the non-blank cells

Regards,
Alan
 
Bill

From XL Help it already does

If an array or reference argument contains text, logical values, or empty
cells, those values are ignored; however, cells with the value zero are
included.
 
Bill,

It should automatically exlude empty cells, excel returns
3 5 9 8 7 5 -> AV = 6.166666667
3 2 5 9 -> AV = 4.75

In both cases it's the same as what I calculate by hand...

Dan E
 
Back
Top