{=AVERAGE(IF(C10:H10<>0,c10:h10)}

[aka_krakur]

I am able to use {=AVERAGE(IF(C10:H10<>0,c10:h10)} to get the average between cells excluding values of zero.
I need help using this same thought process but using a running average between worksheets (still eliminating values of zero).

I've tried using:
{=AVERAGE(IF(C10:H10,'Wk 1 - P'!C10:H10<>0,(C10:H10,'Wk 1 - P'!C10:H10)))}
but I get an Excel help box that pops up stating: "You've entered too many arguments for this function....."

1. Can anyone help
2. Is it even possible to write a formula for such a task as this?

 

[daniels012]

Sorry Sorry I got what you are saying now!!
try:
=SUM('Wk 1 - P:Wk 4 - P'!B10)/=COUNTIF('Wk 3 - P'!C10:H10,">0")+COUNTIF('Wk 4 - P'!C10:H10,">0")+COUNTIF('Wk 2 - P'!C10:H10,">0")+COUNTIF('Wk 1 - P'!C10:H10,">0")

There is probably an easier way but I got this to work

Michael

 

[aka_krakur]

dude....it worked....thanks, you're amazing.

 

[daniels012]

Which Dude??

Michael

 

[aka_krakur]

Let A1:A4 contain a list of your sheet names...

Wk 1 - P
Wk 2 - P
Wk 3 - P
Wk 4 - P

Then, try the following formula...

=SUMPRODUCT(SUMIF(INDIRECT("'"&$A$1:$A$4&"'!C10:H10"),">0"))/SUMPRODUCT(COUNTIF(INDIRECT("'"&$A$1:$A$4&"'!C10:H10"),">0"))
THAT WORKED!! THANKS!!!

 

[aka_krakur]

=SUMPRODUCT(SUMIF(INDIRECT("'"&$A$1:$A$4&"'!C10:H10"),">0"))/SUMPRODUCT(COUNTIF(INDIRECT("'"&$A$1:$A$4&"'!C10:H10"),">0"))

yeah!!! that worked. followed those instructions and boom, it worked.

Thanks