Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count IF excluding blanks or zeroes | Excel Worksheet Functions | |||
Averaging excluding min and max numbers | Excel Worksheet Functions | |||
Using Excel as a Gradebook:Calculating sum excluding blanks and ze | Excel Discussion (Misc queries) | |||
Excluding 0s and blanks from a LINEST function | Excel Worksheet Functions | |||
How to get lowest value excluding blanks | Excel Worksheet Functions |