Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Ignore errors when calculation average of multiple ranges
I'll make this short and brief. Here's what works: {AVERAGE(IF(ISERROR(D4:P4),"",D4:P4))} Here's what I want to do (but doesn't work): {AVERAGE(IF(ISERROR(D4:P4,U4:AG4),"",D4:P4,U4:AG4) )} What am I doing wrong? I want to be able to ignore all errors when calculating an average for _multiple_(2)_ranges_ (D4:P4 and U4:AG4, not just D4:P4). Thanks in advance for the help. -- joshkraemer ------------------------------------------------------------------------ joshkraemer's Profile: http://www.excelforum.com/member.php...o&userid=31508 View this thread: http://www.excelforum.com/showthread...hreadid=511845 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Ignore errors when calculation average of multiple ranges
You should really fix the errors since if you do you can use both ranges in
an average formula =SUM(SUMIF(D4:P4,"<#DIV/0!"),SUMIF(U4:AG4,"<#DIV/0!"))/MAX(1,SUM(COUNTIF(D4:P4,"<#DIV/0!"),COUNTIF(U4:AG4,"<#DIV/0!"))) will work (replace #DIV/0! with the error you can have) but it will fail if you have empty cells since they will be counted -- Regards, Peo Sjoblom Northwest Excel Solutions Portland, Oregon "joshkraemer" wrote in message ... I'll make this short and brief. Here's what works: {AVERAGE(IF(ISERROR(D4:P4),"",D4:P4))} Here's what I want to do (but doesn't work): {AVERAGE(IF(ISERROR(D4:P4,U4:AG4),"",D4:P4,U4:AG4) )} What am I doing wrong? I want to be able to ignore all errors when calculating an average for _multiple_(2)_ranges_ (D4:P4 and U4:AG4, not just D4:P4). Thanks in advance for the help. -- joshkraemer ------------------------------------------------------------------------ joshkraemer's Profile: http://www.excelforum.com/member.php...o&userid=31508 View this thread: http://www.excelforum.com/showthread...hreadid=511845 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Ignore errors when calculation average of multiple ranges
Try...
=AVERAGE(IF(1-ISNUMBER(MATCH(COLUMN(D4:AG4)-COLUMN(D4)+1,{14,15,16,17},0) ),IF(ISNUMBER(D4:AG4),D4:AG4))) ....confirmed with CONTROL+SHIFT+ENTER. Notes: 1) The array constant {14,15,16,17} determines which columns, relative to the first column in your range (Column D), to exclude in your average. In this case, Columns 14 through 17 are excluded. 2) Empty cells will not be counted. Hope this helps! In article , joshkraemer wrote: I'll make this short and brief. Here's what works: {AVERAGE(IF(ISERROR(D4:P4),"",D4:P4))} Here's what I want to do (but doesn't work): {AVERAGE(IF(ISERROR(D4:P4,U4:AG4),"",D4:P4,U4:AG4) )} What am I doing wrong? I want to be able to ignore all errors when calculating an average for _multiple_(2)_ranges_ (D4:P4 and U4:AG4, not just D4:P4). Thanks in advance for the help. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Ignore errors when calculation average of multiple ranges
"joshkraemer" wrote:
Here's what I want to do (but doesn't work): {AVERAGE(IF(ISERROR(D4:P4,U4:AG4),"",D4:P4,U4:AG4) )} [....] I want to be able to ignore all errors when calculating an average for _multiple_(2)_ranges_ General form of the array formula (ctrl-shift-Enter): =average(if(condition1,range1), if(condition2,range2), ...) In your case: =average(if(not(iserror(U2:P4)), U2:P4), if(not(iserror(U4:AG4)), U4:AG4)) PS: Personally, I would avoid the errors within the ranges in the first place. Makes for a less messy spreadsheet. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Ignore errors when calculation average of multiple ranges
Nice! Definitely much simpler and more efficient. Although I would
change it slightly to the following... =AVERAGE(IF(ISNUMBER(D4:P4),D4:P4),IF(ISNUMBER(U4: AG4),U4:AG4)) ....confirmed with CONTROL+SHIFT+ENTER. Three reasons: 1) Your formula seems to count empty cells, whereas this syntax seems to ignore them. 2) It's a little easier to understand. 3) It looks nicer. :) In article , " wrote: "joshkraemer" wrote: Here's what I want to do (but doesn't work): {AVERAGE(IF(ISERROR(D4:P4,U4:AG4),"",D4:P4,U4:AG4) )} [....] I want to be able to ignore all errors when calculating an average for _multiple_(2)_ranges_ General form of the array formula (ctrl-shift-Enter): =average(if(condition1,range1), if(condition2,range2), ...) In your case: =average(if(not(iserror(U2:P4)), U2:P4), if(not(iserror(U4:AG4)), U4:AG4)) PS: Personally, I would avoid the errors within the ranges in the first place. Makes for a less messy spreadsheet. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple Consolidation Ranges | Excel Discussion (Misc queries) | |||
How to Link named ranges from multiple Workbooks into a single Wo. | Excel Discussion (Misc queries) | |||
compare unique identifiers in multiple ranges | Charts and Charting in Excel | |||
Countif & ranges consisting of multiple areas | Excel Worksheet Functions | |||
Countif & ranges consisting of multiple areas | Excel Worksheet Functions |