Averageif error in 2007??

G

Guest

I have been using the averageif formula in 2007.
According to the help file the "range" or "average_range" can include
arrays, However when I have attempted to set an array for evaluation only the
first column is evaluated in the result. I only discovered this when I
compared it to my old method which was to use an array function "average(if".

Is this a problem with the averageif formula or am I missing something?

Larry

Syntax

AVERAGEIF(range,criteria,average_range)

Range is one or more cells to average, including numbers or names, arrays,
or references that contain numbers.

Criteria is the criteria in the form of a number, expression, cell
reference, or text that defines which cells are averaged. For example,
criteria can be expressed as 32, "32", ">32", "apples", or B4.

Average_range is the actual set of cells to average. If omitted, range is
used.
 
G

Guest

Hi Shane, sure here is an example.....

I want to establish the average score for a selected group. In this case
Group 2.

Paste the following array in A1

GROUP Score 1 Score 2 Score 3 Score 4 Score 5
1 1 2 4 3 2
1 3 5 5 5 5
2 4 2 5 4 3
1 1 5 1 1 1
2 4 2 1 4 4
3 1 2 5 2 4
1 5 4 5 2 2
2 1 3 2 1 5
1 2 5 3 5 4
2 3 5 1 4 5
1 1 2 5 5 3
1 1 3 1 4 1
3 4 3 2 5 3
2 5 1 1 4 2
3 2 5 2 3 2

Now compare the results of the following 2 formulas....

The new AverageIf function in 2007 =AVERAGEIF(A2:A16,2,B2:F16)
The array function that I have used =AVERAGE{(IF(A2:A16=2,B2:F16))}

The result of the former is 3.4 which is actually the average of the group 2
scores in column B.

The result of the array formula is 3.04 which is the average of the group 2
scores across the specified array.

Hope this is clear and would welcome your comments.

regards Larry
 
G

Guest

Whoops, just spotted an error in the array function I described.

It SHOULD be....

The array function that I have used {=AVERAGE(IF(A2:A16=2,B2:F16))}

Sorry for the confusion.

regards Larry
 
T

T. Valko

I don't have Excel 2007 but it sounds like AVERAGEIF will only handle a 1
dimensional array, a single row or column (like SUMIF) . The range B2:F16 is
a 2 dimensional array. So, when you try to use a multi-dimensional array
only the leftmost or topmost range is calculated.

Isn't there an AVERAGEIFS function in 2007? I'll bet it's the same, though.

Since your range is relatively small there's no harm in using the array
entered AVERAGE(IF.

Biff
 
G

Guest

Hi Biff, I think you are probably right. I was hoping that it could handle a
2 dimensional array but it doesn't seem to.

Unfortunately the research set that I'm evaluating has thousands of rows X
100 columns which is making the array formula VERY slow.

Was hoping that the averageIf function would solve this. BTW averagIfs IS
the same as far as I can tell.

Thanks for your help

regards Larry
 
R

Roger Govier

Hi

You are correct. Averageifs, has a single array for the Average range,
but multiple arrays for the criteria.

Instead of your array entered formula, you could use
=SUMPRODUCT((A2:A16=2)*B2:F16)/COUNTIF(A2:A16,2)/5
which also returns 3.04
 
G

Guest

Hi Roger, thank you for your input and advice. Your SUMPRODUCT solution is
definitely faster than the array formula I've been using.

With regard the original query, I think someone at MS should explain the 1
dimensional array issue more clearly in their help file :)

Thanks again.

regards Larry
 
R

Ron Rosenfeld

I have been using the averageif formula in 2007.
According to the help file the "range" or "average_range" can include
arrays, However when I have attempted to set an array for evaluation only the
first column is evaluated in the result. I only discovered this when I
compared it to my old method which was to use an array function "average(if".

Is this a problem with the averageif formula or am I missing something?

Larry

Syntax

AVERAGEIF(range,criteria,average_range)

Range is one or more cells to average, including numbers or names, arrays,
or references that contain numbers.

Criteria is the criteria in the form of a number, expression, cell
reference, or text that defines which cells are averaged. For example,
criteria can be expressed as 32, "32", ">32", "apples", or B4.

Average_range is the actual set of cells to average. If omitted, range is
used.

Yes, but the size and shape of Average_range will be the same size and shape as
range, regardless of what you specify for Average_range:

"The actual cells that are averaged are determined by using the top, left cell
in average_range as the beginning cell, and then including cells that
correspond in size and shape to range."


--ron
 
G

Guest

Hi Ron, I don't know if I'm interpreting what you're saying correctly?

I tried setting both range and average_range to a ten column array of equal
size but I am still getting a one dimensional result corresponding to the
first column in the average_range set.

If you have managed to get the AVERAGEIF formula to behave as you describe
I'd welcome an example that I could work through to confirm it.

Thanks Larry
 
R

Ron Rosenfeld

Hi Ron, I don't know if I'm interpreting what you're saying correctly?

I tried setting both range and average_range to a ten column array of equal
size but I am still getting a one dimensional result corresponding to the
first column in the average_range set.

If you have managed to get the AVERAGEIF formula to behave as you describe
I'd welcome an example that I could work through to confirm it.

Thanks Larry

Larry,

I am only reading the HELP screen, not having installed XL2007 as yet.
However, since the description is the same as that for SUMIF in XL2003, I
assume it works similarly. Perhaps the description of sum_range is more
explicit:

"Sum_range are the actual cells to add if their corresponding cells in range
match criteria".

In other words, I don't think AVERAGEIF will do what you want it to do, and
average across multiple columns in average_range, unless those criteria are
present in the corresponding multiple columns in range.

For example, using SUMIF, and the following:

Range: A1:B4

1 1
2 2
3 3
4 4

Sum_range: D1:E4

5 5
5 5
5 5
5 5


=SUMIF(A1:B4,2,D1)

--> 10

Given Range: A1:B4 but with blanks in B1:B4

1
2
3
4


=SUMIF(A1:B4,2,D1)

--> 5


--ron
 
G

Guest

Hi Ron, this is interesting. You are right that this works for the AVERAGEIF
function. It seems to prove that the formula can recognise a 2 dimensional
array but only if the range columns are duplicated to enable this. I'm not
sure what the usual application for such a strategy would be? For my purposes
I would have to duplicate the column I am using as my range in order to match
the column array I'm trying to analyse.

For the immeadiate term I have implemented the SUMPRODUCT solution put
forward by Roger Govier.

Nonetheless I remain interested in getting to the bottom of the AVERAGEIF
and SUMIF array issue.

Thanks for your input and help. You have certainly opened up a whole new
dimension to this.

regards Larry
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top