Help - Easy Also - AverageA

[moosemessier]

I am doing an "averagea" spreadsheet in which information is passed on to other cells on other worksheets once entered.
however - when an empty cell is passed on - the other sheet cannot average because you cannot divide by zero.

what can i do?

[Mark W.]

Use the AVERAGE worksheet function with...

{=AVERAGE(IF(A1:A5),A1:A5)}

Note: This is an array formula which must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula.


[ This Message was edited by: Mark W. on 2002-03-05 13:06 ]

[Aladin Akyurek]

On 2002-03-05 12:57, moosemessier wrote:
I am doing an "averagea" spreadsheet in which information is passed on to other cells on other worksheets once entered.
however - when an empty cell is passed on - the other sheet cannot average because you cannot divide by zero.

what can i do?

If the range you want to average does not contain negative numbers, you can just use:

=SUM(A1:A9)/MAX(1,COUNTIF(A1:A9,">0"))

Otherwise use:

=SUM(A1:A9)/MAX(1,COUNTIF(A1:A9,">0")+COUNTIF(A1:A9,"<0"))

Aladin

[moosemessier]

thanks@ ill try it out.