Array

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!
 
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:M2>0,K2:M2,""))))

Thanks for the quick response:
 
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:M2>0,K2:M2,""))))

Thanks for the quick response:
 
You could leave out the test for 0 when summing.
=IF(COUNTIF(A2:C2,0)=3,0,SUM(A2:C2)/COUNTIF(A2:C2,"<>0"))
 
You could leave out the test for 0 when summing.
=IF(COUNTIF(A2:C2,0)=3,0,SUM(A2:C2)/COUNTIF(A2:C2,"<>0"))
 
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)
 
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)
 
Back
Top