If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Arrary formula for Average includes blank cells
I am using an Average formula (array) on a summary
worksheet to return the average of a set a data from another worksheet (detail) based on location. The worksheet data (detail) is set up as follows; however there are several locations in the entire worksheet. When I use the subtotal function with an autofilter set my data for the account shown below is correct. The Average function under "Other Deliveries" does not include the cells with no values. Location Account Month/Year Stat Routine Other Del Del Del Monticello ABC vendor Apr 04 6 Monticello ABC vendor Apr 04 7 7 Monticello ABC vendor Apr 04 7 6 Monticello DEF Vendor Apr 04 7 6 Monticello DEF Vendor Apr 04 1 1 1 Monticello Spice of Life Apr 04 3 5 2 Average 5.0 5.2 1.5 On the summary worksheet where I have used an array formula to average, it does include the blank cells and returns a different result. Here is the array formula that is entered to return the detail info for "Other del" for the location Monticello. {=IF(ISERROR(AVERAGE(IF((Delivery! $A$2:$A$35="Monticello"),Delivery!$G$2:$G$35))),"" ,AVERAGE (IF((Delivery!$A$2:$A$35="Monticello"),Delivery! $G$2:$G$35)))} Delivery Scores Locations Stat Routine Other Bettendorf 6.5 5.5 6.5 Fort Wayne 6.5 6.0 6.5 Menomonie 7.0 7.0 7.0 Monticello 4.2 5.2 0.5 I need help to revise the formula to return the average result as shown on the detail worksheet. Although the first criteria of the formula exists (Location), I do not want the formula to calculate blank cells. Can someone help me. TIA |
#2
|
|||
|
|||
Arrary formula for Average includes blank cells
Hi
normally AVERAGE would ignore real blank cells automatically -- Regards Frank Kabel Frankfurt, Germany "jan" schrieb im Newsbeitrag ... I am using an Average formula (array) on a summary worksheet to return the average of a set a data from another worksheet (detail) based on location. The worksheet data (detail) is set up as follows; however there are several locations in the entire worksheet. When I use the subtotal function with an autofilter set my data for the account shown below is correct. The Average function under "Other Deliveries" does not include the cells with no values. Location Account Month/Year Stat Routine Other Del Del Del Monticello ABC vendor Apr 04 6 Monticello ABC vendor Apr 04 7 7 Monticello ABC vendor Apr 04 7 6 Monticello DEF Vendor Apr 04 7 6 Monticello DEF Vendor Apr 04 1 1 1 Monticello Spice of Life Apr 04 3 5 2 Average 5.0 5.2 1.5 On the summary worksheet where I have used an array formula to average, it does include the blank cells and returns a different result. Here is the array formula that is entered to return the detail info for "Other del" for the location Monticello. {=IF(ISERROR(AVERAGE(IF((Delivery! $A$2:$A$35="Monticello"),Delivery!$G$2:$G$35))),"" ,AVERAGE (IF((Delivery!$A$2:$A$35="Monticello"),Delivery! $G$2:$G$35)))} Delivery Scores Locations Stat Routine Other Bettendorf 6.5 5.5 6.5 Fort Wayne 6.5 6.0 6.5 Menomonie 7.0 7.0 7.0 Monticello 4.2 5.2 0.5 I need help to revise the formula to return the average result as shown on the detail worksheet. Although the first criteria of the formula exists (Location), I do not want the formula to calculate blank cells. Can someone help me. TIA |
#3
|
|||
|
|||
Arrary formula for Average includes blank cells
"jan" wrote...
... {=IF(ISERROR(AVERAGE(IF((Delivery!$A$2:$A$35="Mon ticello"), Delivery!$G$2:$G$35))),"",AVERAGE(IF((Delivery!$A $2:$A$35="Monticello"), Delivery!$G$2:$G$35)))} ... The problem lies with IF. The IF function when called with an array first argument doesn't/can't return range references as results. Instead, it returns arrays, and in such arrays blank cells are converted to numeric zeros. You need to use something like =IF(COUNTIF(Delivery!$A$2:$A$35,"Monticello"), AVERAGE(IF((Delivery!$A$2:$A$35="Monticello") *ISNUMBER(Delivery!$G$2:$G$35),Delivery!$G$2:$G$35 )),"") -- To top-post is human, to bottom-post and snip is sublime. |
#4
|
|||
|
|||
Arrary formula for Average includes blank cells
Art,
The formula you provided does work. But there is another issue I neglected to mention in my initial message. I have not been able to revise your formula to get it to work. The Summary worksheet is set up to show all locations. If a specific location is not in the detail worksheet (for the month) then the formula returns the Div/# error. How can I adjust the formula that if the location is not in the detail worksheet then "do nothing" in the cell. TIA -----Original Message----- Jan, Interesting problem. I noticed something odd with the array formula. Try the following: =AVERAGE(IF($A$2:$A$13="monticello",IF (D$2$13="","",D213),"")) This basically replaces "" with "" -- which certainly seems ridiculous. But it works on my machine. Art, . |
#5
|
|||
|
|||
Arrary formula for Average includes blank cells
Hi
one way: =IF(ISERROR(AVERAGE(....),"",AVERAGE(...)) -- Regards Frank Kabel Frankfurt, Germany "Jan" schrieb im Newsbeitrag ... Art, The formula you provided does work. But there is another issue I neglected to mention in my initial message. I have not been able to revise your formula to get it to work. The Summary worksheet is set up to show all locations. If a specific location is not in the detail worksheet (for the month) then the formula returns the Div/# error. How can I adjust the formula that if the location is not in the detail worksheet then "do nothing" in the cell. TIA -----Original Message----- Jan, Interesting problem. I noticed something odd with the array formula. Try the following: =AVERAGE(IF($A$2:$A$13="monticello",IF (D$2$13="","",D213),"")) This basically replaces "" with "" -- which certainly seems ridiculous. But it works on my machine. Art, . |
Thread Tools | |
Display Modes | |
|
|