Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
DAS DAS is offline
external usenet poster
 
Posts: 26
Default Averaging excluding blanks--Multiple Tabs

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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,420
Default Averaging excluding blanks--Multiple Tabs

=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.

--
__________________________________
HTH

Bob

"DaS" wrote in message
...
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



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
DAS DAS is offline
external usenet poster
 
Posts: 26
Default Averaging excluding blanks--Multiple Tabs

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!C 4),IF(Wed!C4<0,Wed!C4),IF(Thur!C4<0,Thur!C4)))}




"Bob Phillips" wrote:

=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.

--
__________________________________
HTH

Bob

"DaS" wrote in message
...
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




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Count IF excluding blanks or zeroes Ash Excel Worksheet Functions 2 July 3rd 06 12:40 AM
Averaging excluding min and max numbers n_gineer Excel Worksheet Functions 3 January 12th 06 04:32 PM
Using Excel as a Gradebook:Calculating sum excluding blanks and ze morgan.lintz Excel Discussion (Misc queries) 4 November 1st 05 08:03 PM
Excluding 0s and blanks from a LINEST function Disco Excel Worksheet Functions 4 February 4th 05 05:54 AM
How to get lowest value excluding blanks JohnT Excel Worksheet Functions 5 December 4th 04 11:57 AM


All times are GMT +1. The time now is 06:41 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"