=Average(if( formula using different worksheets

[aka_krakur]

I have no problem using the following formula
{=AVERAGE(IF(C10:H10<>0,C10:H10))} to get the average eliminating values of 0
but what if I have 2 or more worksheets that I need a running daily average
from for example I tried writing the formula as follows:
{=AVERAGE(IF(C10:H10,'Wk 1 - P'!C10:H10<>0,C10:H10,'Wk 1 - P'!C10:H10)}

When I hit <Ctrl> <Shift> <Enter> I get a Microsoft Excel box that pops up
that reads: "You've entered too many arguments for this function. To get
help...."
I have tried re-writing it using more parenthesis and such, but I just can't
seem to figure it out. Is it even possible?

 

[Bruno Campanini]

I have no problem using the following formula
{=AVERAGE(IF(C10:H10<>0,C10:H10))} to get the average eliminating values
of 0
but what if I have 2 or more worksheets that I need a running daily
average
from for example I tried writing the formula as follows:
{=AVERAGE(IF(C10:H10,'Wk 1 - P'!C10:H10<>0,C10:H10,'Wk 1 - P'!C10:H10)}

When I hit <Ctrl> <Shift> <Enter> I get a Microsoft Excel box that pops up
that reads: "You've entered too many arguments for this function. To get
help...."
I have tried re-writing it using more parenthesis and such, but I just
can't
seem to figure it out. Is it even possible?

The IF clause has 4 arguments
C10:H10
'Wk 1 - P'!C10:H10<>0
C10:H10
'Wk 1 - P'!C10:H10
instead of 3.

What do you want to do?

Bruno

 

[aka_krakur]

I am wanting to get the "daily" average in cells c10:h10 in 2 or more
different worksheets (Call them Wk1 & Wk2 let's say)..and I want the average
to exclude zeros as some days of the week have a total of zero for that
particular day.

 

[Ashish Mathur]

Hi,

Try this array formula (Ctrl+Shift+Enter)

{=AVERAGE(IF(and(C10:H10<>0,'Wk 1 - P'!C10:H10<>0),C10:H10,'Wk 1 -
P'!C10:H10))}

Regards,