basic information needed

  • Thread starter Thread starter Carole Greco
  • Start date Start date
C

Carole Greco

I need help with the following on an excel spreadsheet:

I need to average a column of numbers where one on the items might not have
an entry. for example

abcde 5.0
abcd 4.5
abc n/a
ab 4.5
a 4.0

What formula do I put in to make this average? I tried this
(a1+a2+a3+a4+a5)/5 but this does not seem to work.

Can anybody help me???? I have a project due tomorrow (Wednesday) and must
figure this out. Thanks to anyone who can help me.
 
If your numbers are in B1:B5, use the array formula =AVERAGE(IF
(ISNUMBER(B1:B5),B1:B5))

--
An array formula is entered with CTRL-SHIFT-ENTER rather than just
ENTER. If done correctly, XL will display curly brackets { and }
around the formula

Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
How about
=sum(a1:a5)/count(a1:a5)

so long as the blank is actually blank or has text in it the coun
won't inlcude it. If you enter zero in the blank it will be counted
 
Try it with the n/a in the OP entered as =NA()

SUM ignores text and empty fields, but not errors (or #N/As).

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Back
Top