Averaging excluding blanks--Multiple Tabs

[DaS]

I have a tab for each day of the week with a summary tab. The summary tab is
going to average Mon-Thurs and Fri-Sun in two separate columns. I want the
average to exclude the zero's or blanks that have not been filled in yet, so
on Monday, the equation would simply have Monday's information, but when
Tuesday and Wednesday are filled in, it would include them as well as part of
the average.

thanks

 

[Bob Phillips]

=AVERAGE(IF(Fri!A1:A6<>0,Fri!A1:A6),IF(Sat!A1:A10<>0,Sat!A1:A10))

this is an array formula, so commit with Ctrl-Shift-Enter.

likewise for the other.

 

[DaS]

Below is what I've put together, based upon your initial formula, but it's
returning #VALUE!. I'm not sure I've messed up the number of parenthesis, or
what. Also, what I do see is that it's excluding zero's. Some of these
number are going to equal zero, so it needs to just exclude the blanks. That
was a mistake on my part after reviewing my first post.


{=AVERAGE((IF(Mon!C4<>0,Mon!C4),IF(Tue!C4<>0,Tue!C4),IF(Wed!C4<>0,Wed!C4),IF(Thur!C4<>0,Thur!C4)))}