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.

Hi Larees,

Could you send us an example.

Thanks,
Shane

"Larees" <(E-Mail Removed)> wrote in message
news:4ABB55CD-BD5B-4C4F-9F8A-(E-Mail Removed)...
>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.
>
>

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


"Shane Devenshire" wrote:

> Hi Larees,
>
> Could you send us an example.
>
> Thanks,
> Shane
>
> "Larees" <(E-Mail Removed)> wrote in message
> news:4ABB55CD-BD5B-4C4F-9F8A-(E-Mail Removed)...
> >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.
> >
> >
>
>

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

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

"Larees" <(E-Mail Removed)> wrote in message
news:6B07C1B7-6849-418F-86BC-(E-Mail Removed)...
> 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
>
>
> "Shane Devenshire" wrote:
>
>> Hi Larees,
>>
>> Could you send us an example.
>>
>> Thanks,
>> Shane
>>
>> "Larees" <(E-Mail Removed)> wrote in message
>> news:4ABB55CD-BD5B-4C4F-9F8A-(E-Mail Removed)...
>> >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.
>> >
>> >
>>
>>

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

"T. Valko" wrote:

> 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
>
> "Larees" <(E-Mail Removed)> wrote in message
> news:6B07C1B7-6849-418F-86BC-(E-Mail Removed)...
> > 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
> >
> >
> > "Shane Devenshire" wrote:
> >
> >> Hi Larees,
> >>
> >> Could you send us an example.
> >>
> >> Thanks,
> >> Shane
> >>
> >> "Larees" <(E-Mail Removed)> wrote in message
> >> news:4ABB55CD-BD5B-4C4F-9F8A-(E-Mail Removed)...
> >> >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.
> >> >
> >> >
> >>
> >>
>
>
>

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
--
Regards

Roger Govier


"Larees" <(E-Mail Removed)> wrote in message
news:2A5592EA-2FE8-488A-AE9C-(E-Mail Removed)...
> 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
>
> "T. Valko" wrote:
>
>> 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
>>
>> "Larees" <(E-Mail Removed)> wrote in message
>> news:6B07C1B7-6849-418F-86BC-(E-Mail Removed)...
>> > 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
>> >
>> >
>> > "Shane Devenshire" wrote:
>> >
>> >> Hi Larees,
>> >>
>> >> Could you send us an example.
>> >>
>> >> Thanks,
>> >> Shane
>> >>
>> >> "Larees" <(E-Mail Removed)> wrote in message
>> >> news:4ABB55CD-BD5B-4C4F-9F8A-(E-Mail Removed)...
>> >> >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.
>> >> >
>> >> >
>> >>
>> >>
>>
>>
>>

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

"Roger Govier" wrote:

> 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
> --
> Regards
>
> Roger Govier
>
>
> "Larees" <(E-Mail Removed)> wrote in message
> news:2A5592EA-2FE8-488A-AE9C-(E-Mail Removed)...
> > 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
> >
> > "T. Valko" wrote:
> >
> >> 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
> >>
> >> "Larees" <(E-Mail Removed)> wrote in message
> >> news:6B07C1B7-6849-418F-86BC-(E-Mail Removed)...
> >> > 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
> >> >
> >> >
> >> > "Shane Devenshire" wrote:
> >> >
> >> >> Hi Larees,
> >> >>
> >> >> Could you send us an example.
> >> >>
> >> >> Thanks,
> >> >> Shane
> >> >>
> >> >> "Larees" <(E-Mail Removed)> wrote in message
> >> >> news:4ABB55CD-BD5B-4C4F-9F8A-(E-Mail Removed)...
> >> >> >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.
> >> >> >
> >> >> >
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

On Sun, 10 Jun 2007 04:47:00 -0700, Larees <(E-Mail Removed)>
wrote:

>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

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

"Ron Rosenfeld" wrote:

> On Sun, 10 Jun 2007 04:47:00 -0700, Larees <(E-Mail Removed)>
> wrote:
>
> >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
>