Search code examples
excelvbaconditional-statementsaverageworksheet-function

WorksheetFunction.AverageIf of a range to include condition based on cell.interior.color

I am trying to find the average of a column with undefined number (i) of values (range is likely to be between 3-20 cells in column I, row 24 up to undefined number - j = 23 + i). The code previously included only the standard average function.

Now, I'd like it to average those cells in the give range, only including those cells in the proposed range. Hence, ignoring the identified outliers that are above and/ or below LFe, UFe. Location of that averaged value is Range("E" & m).

Is this even possible in the given worksheetfunction? And if so, what do I need to change to get this working?

I have tried to use existing solutions based on conditions such as 'average only cells that are positive' or 'only those that do not include N/A', but can't seem to get it working.

example of data

'Identifying outliers based on interquartile range
qe1 = Application.Quartile(Range("E24:E" & j), 1)
qe3 = Application.Quartile(Range("E24:E" & j), 3)
IQRe = qe3 - qe1
UFe = qe3 + (IQRe * 1.5)
LFe = qe3 - (IQRe * 1.5)

'[EDIT] Currently I have it as follows:
Range("E" & m).Value = Application.WorksheetFunction.AverageIfs(Range("E24:E" & j), "<" & UFe, Range("E24:E" & j), ">" & LFe, Range("E24:E" & j))

Which returns: #VALUE! without the .WorksheetFunction, and 'run error '424' Object required' as written above.

The only option that functioned, BUT not with my variable ranges is formatting it similarly as following: (How can this otherwise be adapted to work for my variable ranges, including 'j' etc.?)

Range("E" & m).Select
ActiveCell.FormulaR1C1 = "=AVERAGEIF(R[-6]C[4]:R[-3]C[4],"">""& 4.3,R[-6]C[4]:R[-3]C[4])"
Solution

  • Not enough rep to comment (sorry!), but here are a few ideas I had:

    1) As SJR has mentioned, if there is a numeric value you're basing the color-coding from, you can use that in your code instead of a color. I'm thinking UFe & LFe are the upper and lower thresholds. So you could just have cells with formulas to automate the UFe & LFe via formulas instead and then use "AVERAGEIFS".

    2) Alternatively, you could integrate your variables into a ".formula" instead, like:

    Sheet1.Range("E" & m).Formula = "=AVERAGEIFS($E$24:$E$" & j & ",$E$24:$E$" & j & ","">" & LFe & """,$E$24:$E$" & j & ",""<" & UFe & """)"

    Just change "Sheet1" to whatever sheet you're referencing