average of non blank cells

R

robert_woodie

i have read somewhere that it is possible to take an average of non
blank cells....is this correct....if so how!!

thanks in advance
Robert.
 
P

Paul

robert_woodie said:
i have read somewhere that it is possible to take an average of non
blank cells....is this correct....if so how!!

thanks in advance
Robert.
Click to expand...

For example, for cells A1:A10, use
=AVERAGE(IF(A1:A10<>"",A1:A10))
entered as an array formula (using CTRL+SHIFT+ENTER rather than just ENTER).
 
L

Leo Heuser

Robert

One way:

=AVERAGE(IF(A2:H12<>"",A2:H12))

The formula is an array formula and must be
entered with <Shift><Ctrl><Enter>.

--
Best Regards
Leo Heuser
Excel MVP

Followup to newsgroup only please.
 
H

Harald Staff

=AVERAGE(A1:A10)
It returns 2 with 2 in A1 and the rest of them empty.
Otherwise is average very easy to do, it's Sum / Count , so it's easy to
roll your own calculations if you find the builtin functions disappointing
one way or another.
 
L

Leo Heuser

Sorry, I meant to show how to calculate
the average and ignoring cells with a zero.

=AVERAGE(IF(A2:H12<>0,A2:H12))

Empty cells are ignored by default as Harald's
answer indicates.

--
Best Regards
Leo Heuser
Excel MVP

Followup to newsgroup only please.

Leo Heuser said:
Robert

One way:

=AVERAGE(IF(A2:H12<>"",A2:H12))

The formula is an array formula and must be
entered with <Shift><Ctrl><Enter>.

--
Best Regards
Leo Heuser
Excel MVP

Followup to newsgroup only please.
Click to expand...
 
Top