[email protected]

[Todd]

Hi, I need to get the average total from a column of
numbers. How do I total them, ignore blank cells and get
the average? I have been trying to use the count,average
and sum functions but can't get it to work yet.


Thanks,


Todd

 

[Peo Sjoblom]

Average disregards blank cells and text.. What doesn't work and what do you
expect
to happen? Do you get errrors?.

 

[Bob Phillips]

Todd,

Try

=AVERAGE(IF((A1:A9<>""),A1:A9))

or

=AVERAGE(IF((NOT(ISBLANK(A1:A9))),A1:A9))

more descriptive but more functions

or

=AVERAGE(IF((NOT(ISBLANK(A1:A9)))*(ISNUMBER(A1:A9)),A1:A9))

in case it contains text

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Dan E]

Todd,

Average should ignore blanks... If you want to be sure though

=SUMIF(B11:B23,"<>""")/--SUMPRODUCT((ISNUMBER(B11:B23)=TRUE)*1)

SUMIF adds them if they're not blank (which wouldn't matter
because I assume blank = 0. Sumproduct counts the number
that are numbers. -> division to average...

BTW, I get the exact same result with =AVERAGE(A1:A100)
and with my calculator.

Dan E

 

[Todd]

Thanks all, I ended up with

=SUM(B10:$B681)/(COUNT(B10:B681)-COUNTIF(B10:B681,0))


T.