Excel 2007, AVERAGEIFS Syntax problem in VBA

[JA3395]

Has anyone successfully used the WorksheetFunction AVERAGEIFS?

I'm having real trouble figuring out the syntax for the function in VBA.

    strLowerDate = Format(dblRunDate + TimeSerial(0, 0, 1), "dd/mm/yyyy hh:mm:ss")
    strUpperDate = Format(dblRunDate + TimeSerial(23, 59, 59), "dd/mm/yyyy hh:mm:ss")

This doesn't work -

   dblResolved = Application.WorksheetFunction.AverageIfs(Range("Resolved"), _
                                                            Range("Language"), Language, _
                                                           Range("Resolved"), ">=" & strLowerDate, _
                                                            Range("Resolved"), "<=" & strUpperDate)

This does work -

   dblResolved = Application.WorksheetFunction.AverageIfs(Range("Resolved"), _
                                                            Range("Language"), Language)

So it's something to do with the string syntax of the criteria for the date range.

NB: Range("Resolved") is a list of Date/Times

Please help!

 

[SkipVought]

Hi,

First, the function is AVERAGEIF NO S.

Second, the funcion has THREE arguments...

Range
Criteria
Sum Range.

I'd suggest getting th function to work first on the sheet, WITHOUT VBA code. Then, with that as a "template" code it.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[JA3395]

Sorry to disagree. In Excel 2007 there is a function AVERAGEIFS.

Similar to AVERAGEIF but it allows multiple conditions.

Try it and see!

 

[JA3395]

I have got it to work on the sheet using EXCEL functions.

=AVERAGEIFS(Resolved,Language,"="&$F$2,Worktype,$F3,Resolved,">="&TEXT(DATE(YEAR(F$5),MONTH(F$5),DAY(F$5))+TIME(0,0,0),"dd/mm/yyyy hh:mm:ss"),Resolved,"<="&TEXT(DATE(YEAR(F$5),MONTH(F$5),DAY(F$5))+TIME(23,59,59),"dd/mm/yyyy hh:mm:ss"))

I'm trying to code it in VBA because by the time I've done IF(ISERROR( etc and done the subtraction of a parallel date, then the algorithm gets very long and complicated, so I'm trying to do it in VBA.

BTW, there is now COUNTIFS and SUMIFS as well....

 

[SkipVought]

I stand corrected!

Arg 1: Range to average
Arg 2: Criteria 1 range
Arg 3: Criteria 1
Are 4 & 5 are sot Criteria 2.

Again, get it working on the sheet.

Is it working there?

Post the FORMULA that is working on your sheet. Chnaces are that you will have to play with the QUOTES in orser to get the date/time string within quotes IN the formula.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[JA3395]

OK, I've solved it, it didn't like the string date.

dblRunDate = DateSerial(Year(RunDate), Month(RunDate), Day(RunDate))
dblLowerDate = dblRunDate + TimeSerial(0, 0, 1)
dblUpperDate = dblRunDate + TimeSerial(23, 59, 59)
    
dblResolved = Application.AverageIfs(Range("Resolved"), _
Range("Language"), Language, _
Range("Worktype"), Worktype, _
Range("Resolved"), ">=" & dblLowerDate, _
Range("Resolved"), "<=" & dblUpperDate)

Why, I don't know, but at least I'm getting a result not an error!

Thanks for any bain-power out there that has been put into this...

 

[JA3395]

Just as a hint, to anyone trying out these WorksheetFunction(s)

If you get the error:

Unable to get the AverageIfs property of the WorksheetFunction Class

Application.WorksheetFunction.AverageIfs()

Then use the code

Application.AverageIfs()

Then you will get the true error being returned when the function is executed...