A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Arrary formula for Average includes blank cells



 
 
Thread Tools Display Modes
  #1  
Old May 14th, 2004, 08:50 PM
jan
external usenet poster
 
Posts: n/a
Default 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  
Old May 14th, 2004, 08:57 PM
Frank Kabel
external usenet poster
 
Posts: n/a
Default 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  
Old May 14th, 2004, 09:58 PM
Harlan Grove
external usenet poster
 
Posts: n/a
Default 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  
Old May 14th, 2004, 11:05 PM
Jan
external usenet poster
 
Posts: n/a
Default 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  
Old May 14th, 2004, 11:19 PM
Frank Kabel
external usenet poster
 
Posts: n/a
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:27 PM.


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