[email protected]

  • Thread starter Thread starter Todd
  • Start date Start date
T

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
 
Average disregards blank cells and text.. What doesn't work and what do you
expect
to happen? Do you get errrors?.
 
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)
 
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
 
Back
Top