Multiple Averageifs

[kasermap]

I am trying to get the average of everything in column K but only when column C ha a particular value. It is working, but if there is no value of "NS" in column C then the whole thing just stops working. How can I get it to continue working and just accept the value doesn't exist?


Range("O2").Value = WorksheetFunction.AverageIfs(Range("K:K"), Range("C:C"), "WTW")
Range("O3").Value = WorksheetFunction.AverageIfs(Range("K:K"), Range("C:C"), "Fuel")
Range("O4").Value = WorksheetFunction.AverageIfs(Range("K:K"), Range("C:C"), "NS")
Range("O5").Value = WorksheetFunction.AverageIfs(Range("K:K"), Range("C:C"), "Expansion")
Range("O6").Value = WorksheetFunction.AverageIfs(Range("K:K"), Range("C:C"), "Minor")

[Moo the Dog]

I'm not a VBA expert, but could this be handled with: On Error Resume Next prior to each command?

- Moo

[Greg M]

Hi there,

See if the following code does what you need - it will enter a value of zero in the Average Cell if none of the tested cells contain the required value:

Please Login or Register  to view this content.

The highlighted values can be altered to suit your own requirements.


I've made some changes to your code:

(a) The Range references are now fully qualified - this means that the code is independent of whichever worksheet happens to be active

(b) The no of rows to be conditionally averaged is specified by the value of the Constant lMAXIMUM_ROWS - it is not very efficient to require Excel to conditionally average almost one and a half million rows each time.

Hope this helps - please let me know how you get on.

Regards,

Greg M