basic information needed

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

 

[Tushar Mehta]

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

 

[BookLady]

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

 

[Tushar Mehta]

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