#### Averageif error in 2007??

```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.

```
 0
Larees (7)
6/10/2007 11:47:00 AM
excel 39879 articles. 2 followers.

11 Replies
755 Views

Similar Articles

[PageSpeed] 29

```Hi Larees,

Could you send us an example.

Thanks,
Shane

"Larees" <Larees@discussions.microsoft.com> wrote in message
news:4ABB55CD-BD5B-4C4F-9F8A-29DF43B4549F@microsoft.com...
>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.
>
>

```
 0
6/10/2007 1:51:55 PM
```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.

regards Larry

"Shane Devenshire" wrote:

> Hi Larees,
>
> Could you send us an example.
>
> Thanks,
> Shane
>
> "Larees" <Larees@discussions.microsoft.com> wrote in message
> news:4ABB55CD-BD5B-4C4F-9F8A-29DF43B4549F@microsoft.com...
> >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.
> >
> >
>
>
```
 0
Larees (7)
6/10/2007 8:50:00 PM
```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
```
 0
Larees (7)
6/10/2007 9:17:00 PM
```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" <Larees@discussions.microsoft.com> wrote in message
news:6B07C1B7-6849-418F-86BC-42CB56C62957@microsoft.com...
> 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.
>
>
> regards Larry
>
>
> "Shane Devenshire" wrote:
>
>> Hi Larees,
>>
>> Could you send us an example.
>>
>> Thanks,
>> Shane
>>
>> "Larees" <Larees@discussions.microsoft.com> wrote in message
>> news:4ABB55CD-BD5B-4C4F-9F8A-29DF43B4549F@microsoft.com...
>> >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.
>> >
>> >
>>
>>

```
 0
biffinpitt (3172)
6/10/2007 9:34:19 PM
```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.

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" <Larees@discussions.microsoft.com> wrote in message
> news:6B07C1B7-6849-418F-86BC-42CB56C62957@microsoft.com...
> > 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.
> >
> >
> > regards Larry
> >
> >
> > "Shane Devenshire" wrote:
> >
> >> Hi Larees,
> >>
> >> Could you send us an example.
> >>
> >> Thanks,
> >> Shane
> >>
> >> "Larees" <Larees@discussions.microsoft.com> wrote in message
> >> news:4ABB55CD-BD5B-4C4F-9F8A-29DF43B4549F@microsoft.com...
> >> >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.
> >> >
> >> >
> >>
> >>
>
>
>
```
 0
Larees (7)
6/10/2007 10:04:01 PM
```Hi

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

=SUMPRODUCT((A2:A16=2)*B2:F16)/COUNTIF(A2:A16,2)/5
which also returns 3.04
--
Regards

Roger Govier

"Larees" <Larees@discussions.microsoft.com> wrote in message
news:2A5592EA-2FE8-488A-AE9C-A755A81F3D9C@microsoft.com...
> 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.
>
>
> 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" <Larees@discussions.microsoft.com> wrote in message
>> news:6B07C1B7-6849-418F-86BC-42CB56C62957@microsoft.com...
>> > 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.
>> >
>> >
>> > regards Larry
>> >
>> >
>> > "Shane Devenshire" wrote:
>> >
>> >> Hi Larees,
>> >>
>> >> Could you send us an example.
>> >>
>> >> Thanks,
>> >> Shane
>> >>
>> >> "Larees" <Larees@discussions.microsoft.com> wrote in message
>> >> news:4ABB55CD-BD5B-4C4F-9F8A-29DF43B4549F@microsoft.com...
>> >> >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.
>> >> >
>> >> >
>> >>
>> >>
>>
>>
>>

```
 0
roger5293 (1125)
6/10/2007 10:56:55 PM
```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.
>
> =SUMPRODUCT((A2:A16=2)*B2:F16)/COUNTIF(A2:A16,2)/5
> which also returns 3.04
> --
> Regards
>
> Roger Govier
>
>
> "Larees" <Larees@discussions.microsoft.com> wrote in message
> news:2A5592EA-2FE8-488A-AE9C-A755A81F3D9C@microsoft.com...
> > 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" <Larees@discussions.microsoft.com> wrote in message
> >> news:6B07C1B7-6849-418F-86BC-42CB56C62957@microsoft.com...
> >> > 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" <Larees@discussions.microsoft.com> wrote in message
> >> >> news:4ABB55CD-BD5B-4C4F-9F8A-29DF43B4549F@microsoft.com...
> >> >> >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.
> >> >> >
> >> >> >
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>
```
 0
Larees (7)
6/10/2007 11:32:00 PM
```On Sun, 10 Jun 2007 04:47:00 -0700, Larees <Larees@discussions.microsoft.com>
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
```
 0
ronrosenfeld (3122)
6/10/2007 11:49:01 PM
```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 <Larees@discussions.microsoft.com>
> 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
>
```
 0
Larees (7)
6/11/2007 7:08:00 AM
```On Mon, 11 Jun 2007 00:08:00 -0700, Larees <Larees@discussions.microsoft.com>
wrote:

>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
```
 0
ronrosenfeld (3122)
6/11/2007 10:14:38 AM
```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

"Ron Rosenfeld" wrote:

> On Mon, 11 Jun 2007 00:08:00 -0700, Larees <Larees@discussions.microsoft.com>
> wrote:
>
> >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
>
```
 0
Larees (7)
6/11/2007 4:58:01 PM

Similar Artilces:

ERROR: Buildversion difference in cdf.initialization.tool
Hi All, We have CRM 1.2 installed and we recently installed the feature pack (i.e the 2 KB files for server and 1 for SFO client). then we installed the migration toolkit and now when we try to run the CDF initialzation tool it says something like: "the build version is not same in MSCRM" Although we are using the migration toolkit from the CD pack of CRM 1.2. i looked at the buildverion table of both MSCRM and CDF_MSCRM databases and found that the major and minor version column have same value i.e 1 & 2 respectively while the value of revision column in MSCRM = 101 while i...

update error
My bank did maintenance on the site which caused a problem in updating accounts. I have been in contact with them and they gave me instructions on how to fix this. They said I needed to stop online services and then set up online services again. My problem is when I go under manage online services and then choose my bank and choose stop online services, nothing happens. I think this is due to the fact that on the account list screen it says update in process next to all the accounts at this bank. Is there a way to stop the current update in process so that I can cancel the online s...

compiling error
I'm using Excel 2000 with Windows 98 second edition. Every time i start and/or exit Excel, there is this message: "compiling error in hidden module Auto Exec New" Has anyone come across the same problem? Please let me know. Thanx --- Message posted from http://www.ExcelForum.com/ On Thu, 5 Feb 2004 04:56:09 -0600, creimke <<creimke.115grj@excelforum-nospam.com>> wrote: >I'm using Excel 2000 with Windows 98 second edition. > >Every time i start and/or exit Excel, there is this message: > >"compiling error in hidden module Auto Exec New&...

Pivot Table
Hi, I have a spreadsheet of data that has information from row 1 to row 54573. I tried to create a Pivot table for these data, but there is a dialog error message pop out when item being pulled to the PivotTable. The dialog error message is "A field in your source data has more unique items than can be used in a PivotTable report. Microsoft Office Excel may not be able to create the report, or may create the report without the data from this field.". There are 10 items in the PivotTable Field List, and 2 of the items are encountering this dialog error message. I'm n...

Error msg said that "Debug Error
Hi all, When i build a dll with a class in my dll #include "myclass2.h" class myclass{ public:// functions private: myclass2 myvariable; } in my app, i included myclass.h, but remove private code as myclass2 myvariable, ex. And my app got that error, But if i include myclass.h into my app, and still keep private code (it mean that my app need myclass.h and myclass2.h), then it is ok. Any idea? Thanks in advance. "Duy Trinh" <duy.trinh@mobinex.com> schrieb im Newsbeitrag news:uPrwtP9qGHA.4996@TK2MSFTNGP04.phx.gbl... > Hi all, > > When i...

error message below
Hello! I have an HP laptop with Windows XP Pro and originally bought it for running karaoke shows! I downloaded some karaoke files off the internet and they show up as HDZIP files; and they used to play beautifully! However, now I can no longer play these particular files and get the following error message: "Unexpected error processing the ZIP file: 0x00000200 C:\DOCUME~1\Computer\LOCALS~1\Temp\auout.zzz" Please advise! Lundo55 wrote: > Hello! > > I have an HP laptop with Windows XP Pro and originally bought it for running > karaoke shows! I d...

CComVariant Error
Hi guys, has anyone got this problem when using Clear() method of a CComVariant that is cause an Access Violation error? any resolution? THanks i think tis will help you to resolve your problem as quoted by MSDN "If the variant to be cleared is a COM object that is passed by reference, the vt field of the pvarg parameter is VT_DISPATCH | VT_BYREF or VT_UNKNOWN | VT_BYREF. In this case, VariantClear does not release the object. Because the variant being cleared is a pointer to a reference to an object, VariantClear has no way to determine if it is necessary to release the object. I...

error in formula
Hello everybody, I'm having a problem with this formula: =1/(1/b1+1/b2+1/b3+1/b4) the problem is that sometimes I only enter two values, in cell b1 and b2. Obviously that generates Div/0 error. How can I "tell" this formula to calculate only cells that have something in it and ignore empty ones. Thanks for help, Mark Try this UDF Public Function nums(ParamArray values()) For i = 0 To UBound(values) If values(i) <> 0 Then newVal = newVal + 1 / values(i) End If Next nums = 1 / newVal End Function Regards, Eddie http://www.HelpE...

Communication Error
Hello and thanks for any replies in advance. I recently upgraded from Money 2002 to Money 2006. I connected to my bank account without a problem but I'm having tremendous difficulty restoring my backup file. When I restore it everything seems to go ok at first, i put in my email and password, my computer churns and then I get a window that states, "A communication error occurred. Please try your call again. If you continue to have.....etc etc." Basically it tells me to contact my "financial institution" which I did and they told me they can't help. Afte...

Page Cannot be Found error when logging in
Hi I have a user account that when I try and login via Web Mail I get a Page Cannot be Found error. I am not having the problem with any other users. If I create a local Outlook profile for this user on my PC I can successfuly access Outlook, but not OWA HTTP Error 404 - File or directory not found. Internet Information Services (IIS) is the exact error I have tried stripping the exchange attributes and deleting the mailbox and recreating the mailbox Any suggestions would be most welcome Scott On Mar 20, 4:16 pm, "Scooty" <sco...@people.net.au> wrote: > Hi > I have a...

Error 1004 when opening a workbook
Dear all, I have EXCEL 2003 and everytime I try and open a file I get Error 1004 "Cannot edit a macro on a hidden workbook. Unhide the workbook using the unhide command". I've gone to the help and done what it says - Set to trust access to Visual Basic Project - but it doesn't do any good. Has anyone got an idea of how to fix this rather irritating problem? many thanks, neil ...

How can I set my Outlook 2007 to save email addresses when sent?
I would like for Outlook to remember email addresses that I send/reply to and give a predicted email addy of the recipient. Is this possible? See if any of this info helps you http://www.slipstick.com/contacts/addauto.asp TX Realtor wrote: >I would like for Outlook to remember email addresses that I send/reply to >and give a predicted email addy of the recipient. Is this possible? -- Message posted via http://www.officekb.com ...

error showing up
error -2147467259 i run a shop with microsoft retail management system as the POS software this error comes up on one of my machines and it wont let me open the application because of this error i have a network set up for the machines(4 of them) to run on. what i had noticed was that this machine was not in the same group as the others and presumed that is why the error was showing. i then changed it into the work group with all the other machines and thought this would have sorted the problem but it did'nt have you any light to shed on this problem. I would guess that there isnt ...

Errors in Archiving
Hi, We previously created an archive of a checking account with an older version of Money and specified the archive to contain from 7-1-95 to 12-31-99. We never checked this before but now we find it continues all the way to November 2003. We just created an archive of our checking account, with Money 2004, and specified it to contain from 12-31-99 to 1-1-05. This time it included all entries up to the present date of 2-4-06. First of all, why has this happened? Is there a way to correct these archives because now they are overlapping. Thaks for any suggestions. jslow -- Dell 4700, Wi...

error message on blue screen during startup and reboot
A problem has been detected and windows has been shut down to prevent damage to your computer. UNMOUNTABLE_BOOT_VOLUME If this is the first time you've seen this stop error screen, restart your computer. If this screen appears again, follow these steps: check to make sure any new hardware or software is properly installed. If this is a new installation, ask your hardware or software manufacturer for any windows updates you might need. If problems continue, disable or remove any newly installed hardware or software. Disable BIOS memory options such as caching or shadowing...

SpamFilter error
I have today suddenly started getting this error when I reply or forward emails messages: I can press OK and bypass it: The signature "SpamFilterSigOL" could not be located. This signature may have been removed. I have recently done an Office update and wonder if this is the cause? Any ideas? Thanks Chris ...

I get this error when i try and run any crystal report>>> that i do not have enough license can any one give me some help please Owen Owen, Check the event log on your CRM server. There is typically a more complete error message there. The Crystal license error can be caused by many different possible problems and the specific msg can help in narrowing it down. "Owen" <owenh@tsol.com> wrote in message news:1175401c383b7\$65b17b90\$a601280a@phx.gbl... I get this error when i try and run any crystal report>>> that i do not have enough license can any on...

I'm using money 2002,I keep getting a download error from my one broker.Somehow the RQ.TXT file gives the DTSTART date as one year ago.This seems to be my problem.How can I fix it? Thanks In microsoft.public.money, Ronald Austen wrote: >I'm using money 2002,I keep getting a download error from >my one broker.Somehow the RQ.TXT file gives the DTSTART >date as one year ago.This seems to be my problem.How can >I fix it? I don't see that RQ DTSTART as a problem. Money is asking for transactions of that date or newer. So your broker should be coming back with it'...

Unable to install Office Professional 2007
Trying to install new Office Pro 2007on brand new computer with Windows 7 & get message "The application was unable to start correctly (0xc0000022)" Read it completely and disable or uninstall the Security program until Office is installed http://www.opssys.com/instantkb/article.aspx?id=10176 -- Peter Please Reply to Newsgroup for the benefit of others Requests for assistance by email can not and will not be acknowledged. "Suzy" <Suzy@discussions.microsoft.com> wrote in message news:9A072076-15DB-497A-9D0D-12ADD468A73C@microsoft.com... &g...

Error message when launch Outlook 2002
The error message "CiceroUIWndFrame:OUTLOOK.EXE-Unable To Locate Component. This application has failed to start because OUTEX.DLL was not found. Re-installing the application may fix problem" The message comes up every time i launch Outlook 2002. After click "OK", mailbox opens like normal. I just migrate from Outlook 2002 POP to Outlook Exchange. I tried reinstall the program. I did not fix the problem. Thanks! this may help .... scroll down past pictures ... http://www.attention-to-details.com/newslog/391-remove-cicerouiwndframe-to-avoid-crashes.asp "jo...

Money 2004 error
Due to other problems I reinstalled Money 2004. Now I can't open it. I'm getting the error message "Unable to load the Registration library(DLL). I have no idea how to get around it. Any help would be greatly appreciated In microsoft.public.money, Rod Sanders wrote: >Due to other problems I reinstalled Money 2004. Now I >can't open it. I'm getting the error message "Unable to >load the Registration library(DLL). I have no idea how to >get around it. Any help would be greatly appreciated See http://support.microsoft.com/default.aspx?scid=kb...

Launching executables from within PowerPoint 2007
Hi, I am running Office 2007 on Windows 7. From within a PowerPoint 2007 presentation I am calling an executable (.EXE) to do something, and then return to the PowerPoint slide. The EXE file is entered into the PowerPoint 2007 slide as an object and this object is activated by the "Activate Contents" option (PowerPoint animation option). However, Windows 7 security presents a warning message with a dialog. See attached image for an example of what it look like when I try to run an executable from within PowerPoint 2007. This warning message is generated by the...

Cannot open this Item Error message
Error (Cannot open this Item) comes up when I do a Search with in my Mail and try to up it. My mailbox is huge and has a lot of folders because I have to keep all my mail. But when I go to where the email is located I can open it with no problems. Bartlomiej...@gmail.com wrote: > Error (Cannot open this Item) comes up when I do a Search with in my > Mail and try to up it. My mailbox is huge and has a lot of folders > because I have to keep all my mail. But when I go to where the email is > located I can open it with no problems. I have Outlook 2003 What version of Outlook and ...