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

In article <FOTsc.61844$(E-Mail Removed)>,
(E-Mail Removed) says...
> 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.
>
>
>
>

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

--
Message posted from http://www.ExcelForum.com

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

In article <(E-Mail Removed)>, BookLady
<<(E-Mail Removed)>> says...
> How about
> =sum(a1:a5)/count(a1:a5)
>
> so long as the blank is actually blank or has text in it the count
> won't inlcude it. If you enter zero in the blank it will be counted.
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>
>