Average Ifs in Excel 2010

[Hans Hamm]

I have tried upteen different variations and cannot get the averageifs to work...
Anyone have an idea what I am doing wrong??


Dim OArng As Range 'Cell where Average is reported
Dim Arng As Range 'Range of Dates to Average
Dim Srng As Range 'Start Date
Dim Erng As Range 'End Date
Set OArng = Sheet1.Range("F3")
Set Srng = Sheet1.Range("C1")
Set Erng = Sheet1.Range("E1")
Set Arng = Sheet2.Range("D2" & Range("AJ" & Rows.Count).End(xlUp).Row)
With OArng
Application.WorksheetFunction.AverageIfs(Arng,Arng>=Sheet1.Range("C1"),Arng<=Sheet1.Range("E1"))

 

[joeu2004]

Anyone have an idea what I am doing wrong??
Dim OArng As Range 'Cell where Average is reported
Dim Arng As Range 'Range of Dates to Average
Dim Srng As Range 'Start Date
Dim Erng As Range 'End Date
Set OArng = Sheet1.Range("F3")
Set Srng = Sheet1.Range("C1")
Set Erng = Sheet1.Range("E1")
Set Arng = Sheet2.Range("D2" & Range("AJ" & Rows.Count).End(xlUp).Row)
With OArng
Application.WorksheetFunction.AverageIfs(Arng,
Arng>=Sheet1.Range("C1"),Arng<=Sheet1.Range("E1"))

The form Arng>=Sheet1.Range("C1") is not even valid in Excel.

In Excel, we would write (assume that Arng is a named range):

AVERAGEIFS(Arng,Arng,">=" & C1,Arng,"<=" & E1)

Likewise in VBA, we would write:

WorksheetFunction.AverageIfs(Arng,Arng,">=" & Range("C1"),Arng,"<=" &
Range("E1"))

 

[joeu2004]

Set Srng = Sheet1.Range("C1")
Set Erng = Sheet1.Range("E1")
Set Arng = Sheet2.Range("D2" & Range("AJ" & Rows.Count).End(xlUp).Row)

[....]
WorksheetFunction.AverageIfs(Arng,Arng,">=" & Range("C1"),
Arng,"<=" & Range("E1"))

Of course, the Range references should be Sheet1.Range references. But
since you set Srng and Erng, we should use them, to wit:

WorksheetFunction.AverageIfs(Arng,Arng,">=" & Srng,Arng,"<=" & Erng)

 

[Hans Hamm]

"wrote:

Set Srng = Sheet1.Range("C1")
Set Erng = Sheet1.Range("E1")
Set Arng = Sheet2.Range("D2" & Range("AJ" & Rows.Count).End(xlUp).Row)

[....]

WorksheetFunction.AverageIfs(Arng,Arng,">=" & Range("C1"),

Arng,"<=" & Range("E1"))

Of course, the Range references should be Sheet1.Range references. But

since you set Srng and Erng, we should use them, to wit:



WorksheetFunction.AverageIfs(Arng,Arng,">=" & Srng,Arng,"<=" & Erng)

Joe,
I just tried that one...
Dim OArng As Range 'Cell Where Average is reported
Dim Arng As Range 'Range of Dates to Average
Dim Srng As Range 'Start Date
Dim Erng As Range 'End Date

Set OArng = Sheet1.Range("F3")
Set Arng = Sheet2.Range("D2" & Range("AJ" & Rows.Count).End(xlUp).Row)
Set Srng = Sheet1.Range("C1")
Set Erng = Sheet1.Range("E1")

With OArng
Application.WorksheetFunction.AverageIfs(Arng,Arng,">="&Srng,Arng,"<="&Erng)
End With

And I get an error stating; Compile error Expected:=
Any ideas? Thanks!

 

[isabelle]

hi,

try replacing:

Set Arng = Sheet2.Range("D2" & Range("AJ" & Rows.Count).End(xlUp).Row)

by

Set Arng = Sheet2.Range("D2" & Sheet2.Range("AJ" & Rows.Count).End(xlUp).Row)


--
isabelle


Le 2012-10-26 18:32, Hans Hamm a écrit :

"wrote:
Set Srng = Sheet1.Range("C1")
Set Erng = Sheet1.Range("E1")
Set Arng = Sheet2.Range("D2" & Range("AJ" & Rows.Count).End(xlUp).Row) [....]

WorksheetFunction.AverageIfs(Arng,Arng,">=" & Range("C1"),
Arng,"<=" & Range("E1"))

Of course, the Range references should be Sheet1.Range references. But

since you set Srng and Erng, we should use them, to wit:



WorksheetFunction.AverageIfs(Arng,Arng,">=" & Srng,Arng,"<=" & Erng)

Joe,
I just tried that one...
Dim OArng As Range 'Cell Where Average is reported
Dim Arng As Range 'Range of Dates to Average
Dim Srng As Range 'Start Date
Dim Erng As Range 'End Date

Set OArng = Sheet1.Range("F3")
Set Arng = Sheet2.Range("D2" & Range("AJ" & Rows.Count).End(xlUp).Row)
Set Srng = Sheet1.Range("C1")
Set Erng = Sheet1.Range("E1")

With OArng
Application.WorksheetFunction.AverageIfs(Arng,Arng,">="&Srng,Arng,"<="&Erng)
End With

And I get an error stating; Compile error Expected:=
Any ideas? Thanks!

 

[joeu2004]

Joe,I just tried that one...
Dim OArng As Range 'Cell Where Average is reported
Dim Arng As Range 'Range of Dates to Average
Dim Srng As Range 'Start Date
Dim Erng As Range 'End Date
Set OArng = Sheet1.Range("F3")
Set Arng = Sheet2.Range("D2" & Range("AJ" & Rows.Count).End(xlUp).Row)
Set Srng = Sheet1.Range("C1")
Set Erng = Sheet1.Range("E1")
With OArng
Application.WorksheetFunction.AverageIfs(Arng,Arng,">="&Srng,Arng,"<="&Erng)
End With
And I get an error stating; Compile error Expected:=

I neglected to correct a syntax error in your original posting. Of course,
the result of AverageIfs should be assigned to something. For example:

Dim x As Double
x = Application.WorksheetFunction.AverageIfs(Arng,Arng,">=" & Srng,Arng,"<="
& Erng)

Also note the spaces around the ampersands (&). VBA seems to correct for
their absence in __this__ context. But in general, they are required; and
VBA does not always make the intended correction. So it would behoove you
to get into the habit of entering the interstitial spaces.

-----

Unrelated observations....


1. The With statement is unnecessary in this context. Alternatively, the
AverageIfs expression is not doing what you intended. As written,
AverageIfs uses ranges that are in column D of Sheet1, not column F (OArng).

But perhaps the With statement is part of a large context.


2. Range("AJ" & Rows.Count).End(xlUp).Row is suspicious. As written, it
looks at column AJ of ActiveSheet, which might be neither Sheet1 nor Sheet2.
But it is using that value to determine the end of column D in Sheet2.

If that is you intention, there is no problem.

But perhaps you want to write either Sheet2.Range("AJ" &
Rows.Count).End(xlUp).Row or the following:

 

[Hans Hamm]

I will look at your points and observations and see if it will work... will let you know,thank you very much!

 

[Hans Hamm]

I will look at your points and observations and see if it will work... will let you know,thank you very much!

Okay, I think I made the changes you and Isabelle recommended. At least I am getting a different error message! "Run-time error '1004": Unable to get the AverageIfs property of the WorksheetFunction class"

I made one error in the Rng and that is the Arng (should be "E" and not "D")
But this is what I have now.

One thing I did not get in your reply was the "with" is not needed. As I understand what I have OArng is where I want the average to be reported (put the average here on Sheet1-F3). So, if I do not use the "with" how do I or am I telling it to do just that?

Dim x As Double
Dim OArng As Range 'Cell Where Average is reported
Dim Arng As Range 'Range of Dates to Average
Dim Srng As Range 'Start Date
Dim Erng As Range 'End Date
Set OArng = Sheet1.Range("F3")
Set Arng = Sheet2.Range("E2:E" & Sheet2.Range("AJ" & Rows.Count).End(xlUp).Row)
Set Srng = Sheet1.Range("C1")
Set Erng = Sheet1.Range("E1")
With OArng
x = Application.WorksheetFunction.AverageIfs(Arng, Arng, ">=" & Srng, "<=" & Erng)
End With

 

[Claus Busch]

Hi Hans,

Am Sat, 27 Oct 2012 06:53:21 -0700 (PDT) schrieb Hans Hamm:

Set OArng = Sheet1.Range("F3")
Set Arng = Sheet2.Range("E2:E" & Sheet2.Range("AJ" & Rows.Count).End(xlUp).Row)
Set Srng = Sheet1.Range("C1")
Set Erng = Sheet1.Range("E1")

Sheet1.Range("E1") is not valid. Change the code above to:

With Sheets("Sheet1")
Set OArng = .Range("F3")
Set Srng = .Range("C1")
Set Erng = .Range("E1")
End With
Set Arng = Sheets("Sheet2").Range("E2:E" & _
Sheets("Sheet2").Range("AJ" & Rows.Count).End(xlUp).Row)


Regards
Claus Busch

 

[joeu2004]

I am getting a different error message!
"Run-time error '1004": Unable to get the AverageIfs
property of the WorksheetFunction class"

I don't recall if you ever told us what version of Excel you are using.

AverageIfs is valid only in Excel 2007 and later.

If you are using Excel 2007 and later, I believe that misleading runtime
error can actually mean that are no cells in Arng that meets both
conditions. In other words, the runtime error is the same as the #DIV/0
error we would get in Excel.

I suggest that you try using AVERAGEIFS in an Excel formula with the same
ranges to see if you do, in fact, get a #DIV/0 error. Note: This is just
for debugging purposes.

If you might expect #DIV/0 errors (i.e. AVERAGEIFS of a null range), you can
avoid the runtime error in one of two ways.

On Error Resume Next
Range("a1") = WorksheetFunction.AverageIfs(...)
If Err > 0 Then Range("a1") = 0 ' this might be redundant
On Error GoTo 0

or

On Error Resume Next
Range("a1") = Application.AverageIfs(...)
If Err > 0 Then Range("a1") = 0 ' optional
On Error GoTo 0

In the first case, VBA might store a zero into Range("a1") automagically.

In the second case, VBA will store a #DIV/0 (?) error into Range("a1").
That is the difference between Application.AverageIfs and
(Application.)WorksheetFunction.AverageIfs: Application.AverageIfs returns
"soft" errors in the form of Excel error codes, e.g. xlErrDiv0.

However, IIRC, Application.AverageIfs is deprecated. That is, it is a
legacy feature, and it might be obsoleted in the future. So arguably,
WorksheetFunction.AverageIfs is better to use.

Set Arng = Sheet2.Range("E2:E" & Sheet2.Range("AJ" &
Rows.Count).End(xlUp).Row)
Set Srng = Sheet1.Range("C1")
Set Erng = Sheet1.Range("E1") [....]
x = Application.WorksheetFunction.AverageIfs(Arng, Arng, ">=" & Srng, "<="
& Erng)

I assume that Sheet1 and Sheet2 are valid VBA __objects__. If they weren't,
I would expect a runtime error before the AverageIfs usage.

If Sheet1 and Sheet2 are valid VBA objects, there is __nothing__ wrong
syntactically with Sheet1.Range and Sheet2.Range.

Howevever, the __object__ names are not necessarily the same as __sheet__
names. So the __objects__ Sheet1 and Sheet2 might not be the intended
worksheets.

You might have intended to write Sheets("Sheet1").Range and
Sheets("Sheet2").Range instead.

Look carefully at the information in the Project Explorer. If I have time
later, I will provide an image to explain this better.

One thing I did not get in your reply was the "with"
is not needed. As I understand what I have OArng is
where I want the average to be reported (put the average
here on Sheet1-F3).

The With construct has nothing to do with where a value is assigned.

The With construct is only a syntax shorthand. For example:

With Range("a1")
.Value = 123
.HorizontalZAlignment = xlLeft
End With

is a shorthand for Range("a1").Value. Use the With construct when you want
to access multiple properties of an object. But IMHO, we should use the
With construct sparingly because it is error-prone: note the need to put a
period (.) before each property.

If you want to assign the AverageIfs result to OArng, use the following
without the With construct:

OArng = Application.WorksheetFunction.AverageIfs(Arng, Arng, ">=" & Srng,
"<=" & Erng)

 

[joeu2004]

If Sheet1 and Sheet2 are valid VBA objects, there is __nothing__ wrong
syntactically with Sheet1.Range and Sheet2.Range. [....]
Look carefully at the information in the Project Explorer.
If I have time later, I will provide an image to explain
this better.

See https://www.box.com/s/tic8gdjyhcljog10q9c1.

 

[Hans Hamm]

If Sheet1 and Sheet2 are valid VBA objects, there is __nothing__ wrong

syntactically with Sheet1.Range and Sheet2.Range.
[....]

Look carefully at the information in the Project Explorer.

If I have time later, I will provide an image to explain

this better.

See https://www.box.com/s/tic8gdjyhcljog10q9c1.

Joe,
I am looking at Claus' response also; but I am running Excel 2010.
I do indeed get the #DIV/0! error in the formula.
Just looking at the formula this is what I am trying to do.
In Sheet 1, Cell C1 there is a beginning date 'lets say it is 8/13/2012
In Sheet 1, Cell E1 there is an ending date ' say 10/25/2012
The cell I need the calculation done is Sheet 1 F3
Sheet 2 column E are where the # of attempts are recorded and
Sheet 2 column AJ are the dates when the record was created

So, =AVERAGEIFS(Sheet2!E:E,Sheet2!AJ:AJ,Sheet1!C1,Sheet2!AJ:AJ,Sheet1!E1)
Returns #DIV/0!

But if I change it to
=AVERAGEIFS(Sheet2!E:E,Sheet2!AJ:AJ,">=8/13/2012",Sheet2!AJ:AJ,"<=10/25/2012")
I get the average 2.0

Hmmmmmm.

 

[joeu2004]

I do indeed get the #DIV/0! error in the formula.

With the WorksheetFunction.AverageIfs expression, or just with the
(incorrect) Excel formula below?

Just looking at the formula this is what I am trying to do.
In Sheet 1, Cell C1 there is a beginning date 'lets say it is 8/13/2012
In Sheet 1, Cell E1 there is an ending date ' say 10/25/2012
The cell I need the calculation done is Sheet 1 F3
Sheet 2 column E are where the # of attempts are recorded and
Sheet 2 column AJ are the dates when the record was created

So, =AVERAGEIFS(Sheet2!E:E,Sheet2!AJ:AJ,Sheet1!C1,Sheet2!AJ:AJ,Sheet1!E1)
Returns #DIV/0!

Of course. This is nonsensical. It says "average E:E wherever AJ:AJ equals
C1 __and__ AJ:AJ equals E1". Of course, AJ:AJ can never __equal__ two
different values.

But if I change it to
=AVERAGEIFS(Sheet2!E:E,Sheet2!AJ:AJ,">=8/13/2012",Sheet2!AJ:AJ,"<=10/25/2012")
I get the average 2.0

And that is __almost__ what you wrote in VBA. You wrote:

Application.WorksheetFunction.AverageIfs(Arng, Arng, ">=" & Srng, "<=" &
Erng)

Note the correct use of ">=" and "<=", just as you wrote in Excel.

But I just noticed a syntax error in your VBA expression that I had
overlooked before.

And I notice an important difference between your Excel and VBA expressions.
Assuming the Excel formula reflects your intention, the VBA code should be
(you should choose better variable names) (beware of line wrap in the
following):

Dim OArng As Range 'Cell Where Average is reported
Dim Arng As Range 'Range of __data__ to Average <<<<<
Dim Drng As Range 'Range of __dates__ to be compared <<<<<
Dim Srng As Range 'Start Date
Dim Erng As Range 'End Date
Dim lastRow As Long '<<<<<
lastRow = Sheet2.Range("AJ" & Rows.Count).End(xlUp).Row '<<<<<
Set OArng = Sheet1.Range("F3")
Set Arng = Sheet2.Range("E2:E" & lastRow)
Set Drng = Sheet2.Range("AJ2:AJ" & lastRow) '<<<<<
Set Srng = Sheet1.Range("C1")
Set Erng = Sheet1.Range("E1")
On Error Resume Next
OArng = WorksheetFunction.AverageIfs(Arng, Drng, ">=" & Srng, Drng, "<=" &
Erng)
If Err > 0 Then OArng = 0
On Error GoTo 0

Suggestion: temporarily comment out the statement On Error Resume Next to
be sure that no unexpected error occurs.

If that still does not work, there are still differences between the Excel
and VBA formulas to consider. And of course, there is still the question of
Sheet1 v. Sheets("Sheet1"). But let's "cross that bridge when we come to
it", if it is necessary at all.

PS: Resist the temptation to use E:E and AJ:AJ as the ranges for
WorksheetFunction.AverageIfs. That form unnecessarily forces Excel to look
at 1+ million rows instead of the minimum number of rows as you wrote in
VBA, namely "E2:E"&lastRow.

Question: Why are you using VBA for this in the first place?

If it is in order to use "E2:E"&lastRow instead of E:E, there are ways to
accomplish the same thing in Excel.

The advantage of doing it in Excel is: Excel will recalculate
automagically. Relying on a VBA macro requires that you recalculate
manually (or using an event macro).

 

[Hans Hamm]

With the WorksheetFunction.AverageIfs expression, or just with the

(incorrect) Excel formula below?














Of course. This is nonsensical. It says "average E:E wherever AJ:AJ equals

C1 __and__ AJ:AJ equals E1". Of course, AJ:AJ can never __equal__ two

different values.







And that is __almost__ what you wrote in VBA. You wrote:



Application.WorksheetFunction.AverageIfs(Arng, Arng, ">=" & Srng, "<=" &

Erng)



Note the correct use of ">=" and "<=", just as you wrote in Excel.



But I just noticed a syntax error in your VBA expression that I had

overlooked before.



And I notice an important difference between your Excel and VBA expressions.

Assuming the Excel formula reflects your intention, the VBA code should be

(you should choose better variable names) (beware of line wrap in the

following):



Dim OArng As Range 'Cell Where Average is reported

Dim Arng As Range 'Range of __data__ to Average <<<<<

Dim Drng As Range 'Range of __dates__ to be compared <<<<<

Dim Srng As Range 'Start Date

Dim Erng As Range 'End Date

Dim lastRow As Long '<<<<<

lastRow = Sheet2.Range("AJ" & Rows.Count).End(xlUp).Row '<<<<<

Set OArng = Sheet1.Range("F3")

Set Arng = Sheet2.Range("E2:E" & lastRow)

Set Drng = Sheet2.Range("AJ2:AJ" & lastRow) '<<<<<

Set Srng = Sheet1.Range("C1")

Set Erng = Sheet1.Range("E1")

On Error Resume Next

OArng = WorksheetFunction.AverageIfs(Arng, Drng, ">=" & Srng, Drng, "<=" &

Erng)

If Err > 0 Then OArng = 0

On Error GoTo 0



Suggestion: temporarily comment out the statement On Error Resume Next to

be sure that no unexpected error occurs.



If that still does not work, there are still differences between the Excel

and VBA formulas to consider. And of course, there is still the question of

Sheet1 v. Sheets("Sheet1"). But let's "cross that bridge when we come to

it", if it is necessary at all.



PS: Resist the temptation to use E:E and AJ:AJ as the ranges for

WorksheetFunction.AverageIfs. That form unnecessarily forces Excel to look

at 1+ million rows instead of the minimum number of rows as you wrote in

VBA, namely "E2:E"&lastRow.



Question: Why are you using VBA for this in the first place?



If it is in order to use "E2:E"&lastRow instead of E:E, there are ways to

accomplish the same thing in Excel.



The advantage of doing it in Excel is: Excel will recalculate

automagically. Relying on a VBA macro requires that you recalculate

manually (or using an event macro).

Joe,
Not sure if you ever saw Bruce Almighty... the line is "B E A Utiful"! It works to perfection.
Have not read all the details of what you wrote as I am a little behind today, but I will a little later to understand all your details and advice.

As to why VBA?
This is just part of a larger project and it basically comes down to this.
When we use formulas it is a copy down the rows/across the columns then paste special etc... a lot of manual work involved in this report and more reports to work on. So I am trying to come up with as much as possible to be automatic.

I noticed this.. what happens when you want to use more than just two arguments ie. "Arg1 As Range, Arg2 As Range" for AverageIfs?

 

[joeu2004]

As to why VBA?
This is just part of a larger project

Okay; I expected as much. Just wanted to be sure.

I noticed this.. what happens when you want to use
more than just two arguments ie.
"Arg1 As Range, Arg2 As Range" for AverageIfs?

I believe you are referring to the VBA "quick info" hints while you are
typing.

They are very generic and not very useful. Don't read too much into them.

Instead, you should rely on the Excel help pages for information about the
parameters and behavior of any particular VBA WorksheetFunction.

(Hopefully they are same. I think I did stumble upon a difference in one
XL2003 VBA WorksheetFunction. But I would consider that a defect, not a
common thing.)

 

[Hans Hamm]

Okay; I expected as much. Just wanted to be sure.













I believe you are referring to the VBA "quick info" hints while you are

typing.



They are very generic and not very useful. Don't read too much into them.



Instead, you should rely on the Excel help pages for information about the

parameters and behavior of any particular VBA WorksheetFunction.



(Hopefully they are same. I think I did stumble upon a difference in one

XL2003 VBA WorksheetFunction. But I would consider that a defect, not a

common thing.)

That is exactly what I was reading and it did not make sense... as why there are only two argument/ranges.
I appreciate all of your help and I would ask that you keep an eye on me as I am sure I will get into another on of the predicaments and will need help. Most answers I can find and/or deduce from the group, but....
Once again I thank you for all of your help!