average of non blank cells

[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.

 

[Paul]

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.

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).

 

[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.

 

[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.

 

[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.

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.