Trying to calculate the average, min and max of a range of data depending their ID

Question

I have 2 columns. One with name, column A, (COV, COSV, ETA...) and the second with number. I have around 40000 row with 30 dirrerent name in column A. i have in a other sheet all the different name in the column A.

I want to calculate the average result, the min values and the max values for each name in column A. SO the average result of all the number in column B for each COV by exemple.

I was able to caculate the average result with the line in VBA without any problem.

WorksheetFunction.AverageIf

But i don't find any mean to made the same thing for the min and max values of column B.

The coding need to be in vba.

Any idea?

Sebastien

Here is part of the code

sub delais

Worksheets("delais_moyen").Select

lastrow = Range("C4", Range("C4").End(xlDown)).Rows.Count + 3

Worksheets(message3).Select

lastline = Range("D7", Range("D7").End(xlDown)).Rows.Count + 6

columnlettermin = Split(Cells(1, lastcol).Address, "$")(1)
columnlettermoy = Split(Cells(1, lastcol + 1).Address, "$")(1)
columnlettermax = Split(Cells(1, lastcol + 2).Address, "$")(1)

Worksheets("delais_moyen").Select

For j = 4 To lastrow

    Set reponse = Sheets(message2).Range("D7:D" & lastline)

    Set delais = Sheets(message2).Range("P7:P" & lastline)

    reponsemin = columnlettermin & j

    reponsemoy = columnlettermoy & j

    reponsemax = columnlettermax & j


Range(columnlettermoy & j) = WorksheetFunction.AverageIf(reponse, Range("C" & j), delais)

 next j

end sub

So it sounds like your version of Excel doesn't support MINIFS and MAXIFS? — BigBen, Apr 08 '20 at 14:34
I think it does, excel 2013, but was not able to make it work. Any good advice? My level in vba programming is average at best. — sebasien, Apr 08 '20 at 14:46
MInIf and MaxIf both take the same parameters that AverageIf does. Show us what you've tried, so we can see where the problem may lie. — Frank Ball, Apr 08 '20 at 14:58
`MINIFS` and `MAXIFS` are not available in Excel 2013 though. — BigBen, Apr 08 '20 at 15:04
Just looked at it and minifs and maxifs seem to be accessible only in excel 2016+. Sorry for the confusion but my excel is in french. Any idea without them — sebasien, Apr 08 '20 at 15:12
Use this ARRAY formula: '=AVERAGE(IF(Sheet1!A:A=A1;Sheet1!B:B)) CTRL+SHIFT+ENTER'. Do the same for MAX and MIN. — VBasic2008, Apr 08 '20 at 15:37
You can edit your question, so if you could add the code for the AVERAGE solution, it would make it easier to write the MAX and MIN solutions in VBA. — VBasic2008, Apr 08 '20 at 15:51
Take a look at this - https://stackoverflow.com/questions/41031524/create-vba-code-for-maxifs — Frank Ball, Apr 08 '20 at 16:53
Seem to be what i need but trying to understand the coding and how it wrok to apply it to my situation. — sebasien, Apr 08 '20 at 19:32

score 0 · Answer 1 · answered Apr 08 '20 at 22:10

First and foremost, avoid using .Select and even .Activate, .ActiveCell, .ActiveSheet, .ActiveWorkbook as discussed here How to avoid using Select in Excel VBA.

Second, consider the formula array functions for MINIFs and MAXIFs as comments indicate in VBA then convert to final values. NOTE: Cell references below may need adjustments as you appear to be working across different worksheets:

With Worksheets(message3)
    lastline = .Range("D7", .Range("D7").End(xlDown)).Rows.Count + 6

    columnlettermin = Split(.Cells(1, lastcol).Address, "$")(1)
    columnlettermoy = Split(.Cells(1, lastcol + 1).Address, "$")(1)
    columnlettermax = Split(.Cells(1, lastcol + 2).Address, "$")(1)
End With

With Worksheets("delais_moyen")

    lastrow = .Range("C4", .Range("C4").End(xlDown)).Rows.Count + 3

    For j = 4 To lastrow
        ' CREATE AGGREGATE ARRAY FORMULAS
        .Range(columnlettermoy & j).FormulaArray = "=AVERAGE(IF(" & message2 & "!$P$2:$P$" & lastline & "=delais_moyen!C" & j & ", " & message2 & "!$D$2:$D$" & lastline & "))"
        .Range(columnlettermin & j).FormulaArray = "=MIN(IF(" & message2 & "!$P$2:$P$" & lastline & "=delais_moyen!C" & j & ", " & message2 & "!$D$2:$D$" & lastline & "))"
        .Range(columnlettermax & j).FormulaArray = "=MAX(IF(" & message2 & "!$P$2:$P$" & lastline & "=delais_moyen!C" & j & ", " & message2 & "!$D$2:$D$" & lastline & "))"

        ' CONVERT CELL FORMULAS TO VALUES
        .Range(columnlettermoy & j) = .Range(columnlettermoy & j).Value
        .Range(columnlettermin & j) = .Range(columnlettermin & j).Value
        .Range(columnlettermax & j) = .Range(columnlettermax & j).Value
    Next j

End With

Trying to calculate the average, min and max of a range of data depending their ID

1 Answers1