Formula

[BKG SPORTS]

EXCEL 2007

I'm trying to average out a1-a10. The result in a11.I have results of
another formula in a1-a10, it won't average out. the result I get is
#n/a. What would make it average out

thanks for any help
BKG

 

[Aladin Akyurek]

EXCEL 2007

I'm trying to average out a1-a10. The result in a11.I have results of
another formula in a1-a10, it won't average out. the result I get is
#n/a. What would make it average out

thanks for any help
BKG

Do you have any #N/A in A1:A10? If so, try:

=AVERAGE(IF(ISNUMBER(A1:A10),A1:A10))

which needs to be confirmed with control+shift+enter, not just enter.

Anther option:

=SUM(SUMIF(A1:A10,{"<0",">0"}))/SUM(COUNTIF(A1:A10,{"<0",">=0"}))

which needs just enter.

 

[BKG SPORTS]

Hey Aladin, thanks much, that worked out well. Don't
understand about the cenral+shift+enter, but it worked great

 

[Ron99]

When its an array function need to enter it as ctrl+shift+enter

 

[Aladin Akyurek]

Hey Aladin, thanks much, that worked out well.

Fine. Thanks for the feedback. The second formula does not need control+shift+enter (CSE) and that can be shortened to:

=SUM(SUMIF(A1:A10,{"<0",">0"}))/COUNT(A1:A10)

[quit]Don't
understand about the cenral+shift+enter, but it worked great

CSE is required in order to signal Excel that the formula in question needs to process array objects. See Help under "arrays".