Array
 

This is what I'm trying to do, but it wont work. I have a workbook with over
500 entries and my formula is an array. Is there a way to do the arrays w/o
having to do the Ctrl+Shift+Enter each time?

Example:
0 5 5 my formula is =AVERAGE(IF(a2:c2<0,a2:c2,""))

Also, is there a way to make the array return 0 if all the columns are 0? I
can't seem to get it to work.

Thanks!

Array
 

=IF(COUNTIF(A2:C2,0)=3,0,SUMPRODUCT(--(A2:C2<0),A2:C2)/COUNTIF(A2:C2,"<0"))

------------------
mama no teeth

"Lisa" wrote:

This is what I'm trying to do, but it wont work. I have a workbook with over
500 entries and my formula is an array. Is there a way to do the arrays w/o
having to do the Ctrl+Shift+Enter each time?

Example:
0 5 5 my formula is =AVERAGE(IF(a2:c2<0,a2:c2,""))

Also, is there a way to make the array return 0 if all the columns are 0? I
can't seem to get it to work.

Thanks!

Array
 

I dont think that would work since the 0s are still there. But I figured
out....light bulb just goes off...here's my formula:

=IF(K2+L2+M2=0,0,(AVERAGE(IF(K2:M20,K2:M2,""))))

Thanks for the quick response:

"Lisa" wrote:

This is what I'm trying to do, but it wont work. I have a workbook with over
500 entries and my formula is an array. Is there a way to do the arrays w/o
having to do the Ctrl+Shift+Enter each time?

Example:
0 5 5 my formula is =AVERAGE(IF(a2:c2<0,a2:c2,""))

Also, is there a way to make the array return 0 if all the columns are 0? I
can't seem to get it to work.

Thanks!

Array
 

You could leave out the test for 0 when summing.
=IF(COUNTIF(A2:C2,0)=3,0,SUM(A2:C2)/COUNTIF(A2:C2,"<0"))


"Nobody" wrote:

=IF(COUNTIF(A2:C2,0)=3,0,SUMPRODUCT(--(A2:C2<0),A2:C2)/COUNTIF(A2:C2,"<0"))

------------------
mama no teeth

"Lisa" wrote:

This is what I'm trying to do, but it wont work. I have a workbook with over
500 entries and my formula is an array. Is there a way to do the arrays w/o
having to do the Ctrl+Shift+Enter each time?

Example:
0 5 5 my formula is =AVERAGE(IF(a2:c2<0,a2:c2,""))

Also, is there a way to make the array return 0 if all the columns are 0? I
can't seem to get it to work.

Thanks!

Array
 

Actually, I think the first conditional test could also be left out and use
MAX to avoid DIV/0 errors
=SUM(A3:C3)/MAX(COUNTIF(A3:C3,"<0"),1)

"JMB" wrote:

You could leave out the test for 0 when summing.
=IF(COUNTIF(A2:C2,0)=3,0,SUM(A2:C2)/COUNTIF(A2:C2,"<0"))


"Nobody" wrote:

=IF(COUNTIF(A2:C2,0)=3,0,SUMPRODUCT(--(A2:C2<0),A2:C2)/COUNTIF(A2:C2,"<0"))

------------------
mama no teeth

"Lisa" wrote:

This is what I'm trying to do, but it wont work. I have a workbook with over
500 entries and my formula is an array. Is there a way to do the arrays w/o
having to do the Ctrl+Shift+Enter each time?

Example:
0 5 5 my formula is =AVERAGE(IF(a2:c2<0,a2:c2,""))

Also, is there a way to make the array return 0 if all the columns are 0? I
can't seem to get it to work.

Thanks!