Help with MAXIFS and MINIFS

[JARichard74]

The following formula seems to work. =MINIFS(F4:X4,$G$7:$Y$7,"COMPLIANT"). In the range F4:X4, I want to find the MIN of F4,H4,J4,L4,N4,P4,R4,T4,V4,X4 if the condition applies. (I'll want to do the MAXIFS as well)
I use the following VBA to add rows to the table

Do Until z = 0
Set tblRow = tbl.ListRows.Add
tblRow.Range.Offset(-1).Copy
tblRow.Range.PasteSpecial xlPasteFormulasAndNumberFormats
z = z - 1
Loop

The code works well except that the MINIFS formulas get changed to F4:X9, F5:X10, F6:X11, etc. instead of F4:X4, F5:X5, F6:X6, etc.

Need help to accomplish this, not necessarily with using MINIFS or MAXIFS. Thanks

 

[jasonb75]

You latest proposed AGGREGATE formula did not work. For some reason Excel does not seem to like R7C7:R7C25,it wants to change it to RC77:RC725

If you look, my reply contained vba code not formulas, it is to use in place of your original copy and paste code.
The formulas are written in R1C1 notation, so if you try to enter them directly into excel then it will not work (unless you change settings so that R1C1 is accepted, but I wouldn't advise doing that).

Using R1C1 in formulas, R7C7 means row 7, column 7 (i.e. G7) as an absolute reference. When there is no number between R and C, i.e. RC6:RC24, it tells the formula to look at the row that it is in, so in D4, RC6 translates to F4, in D5, RC6 translates to F5, etc. Again, this only works if you apply the formula through vba, typing RC6 into a cell will treat RC as a regular column, not a row and column intersection.

 

[JARichard74]

I get a compile error, Syntax error. Have not been able to figure out what the error is

 

[jasonb75]

Oops, that was my bad, I forgot to double up the double quotes in the formulas, it should have been

Range("D4:D8").FormulaR1C1 = "=IFERROR(AGGREGATE(15,6,RC6:RC24/(RC6:RC24<>"""")/(R7C7:R7C25=""COMPLIANT""),1),""No Data"")"
Range("E4:E8").FormulaR1C1 = "=IFERROR(AGGREGATE(14,6,RC6:RC24/(RC6:RC24<>"""")/(R7C7:R7C25=""COMPLIANT""),1),""No Data"")"

I've also added an error trap to cover the #NUM! errors in the sheet when there is no data meeting the criteria, i.e all value cells are empty and/or none of the cells in row 7 = 'COMPLIANCE'

The code is based on your original formula where 'COMPLIANCE' was specified in row 7, however I note that in your data samples, that actually appears in row 11. It is not entirely clear which is correct. If it should be row 11 then you need to change R7C to R11C in the code (4 places).

 

[JARichard74]

Wow, early riser! Yes, the COMPLIANCE row starts at row 7 and then moves based on the number of rows I add to the table. The D4:D8 and E4:E8 is fine for testing however I will eventually have to figure out to fill out the table from D4 and E4 to the last row. I probably have a coding error in my script to create the Table. If I use you code exactly then my script runs and I have the columns with No Data which makes sense as there is no data. However, as I type data in the first cell I get a circular reference error and the formulas do not work. If I remove the IFERROR check from your formula then my script blocks on that line of code. When I remove your code, the script runs correctly.

Sub SetupCalc()
Const MyPassword As String = "dcccdc"
Dim ChangeProtection As Boolean
Dim z As Integer
Dim tbl As ListObject
Dim tblRow As ListRow
Set tbl = ThisWorkbook.Worksheets("Calc").ListObjects("Results")

' Removes the Protection
With Sheets("Calc")
    If .ProtectContents = True Then
        .Unprotect (MyPassword)
        ChangeProtection = True
    End If
' Check that the Bidders and Items tables are created in sheet Prep
If WorksheetFunction.CountA(ThisWorkbook.Worksheets("Prep").Range("G1")) = 0 Then
    MsgBox "Please setup your bidders first"
    Exit Sub
End If
If WorksheetFunction.CountA(ThisWorkbook.Worksheets("Prep").Range("G3")) = 0 Then
    MsgBox "Please setup your items first"
    Exit Sub
End If
' Copies the names of the Bidders from the Bidders Table in sheet Prep

ThisWorkbook.Worksheets("Calc").Range("F2").Value = ThisWorkbook.Worksheets("Prep").Range("B9").Value
ThisWorkbook.Worksheets("Calc").Range("H2").Value = ThisWorkbook.Worksheets("Prep").Range("B10").Value
ThisWorkbook.Worksheets("Calc").Range("J2").Value = ThisWorkbook.Worksheets("Prep").Range("B11").Value
ThisWorkbook.Worksheets("Calc").Range("L2").Value = ThisWorkbook.Worksheets("Prep").Range("B12").Value
ThisWorkbook.Worksheets("Calc").Range("N2").Value = ThisWorkbook.Worksheets("Prep").Range("B13").Value
ThisWorkbook.Worksheets("Calc").Range("P2").Value = ThisWorkbook.Worksheets("Prep").Range("B14").Value
ThisWorkbook.Worksheets("Calc").Range("R2").Value = ThisWorkbook.Worksheets("Prep").Range("B15").Value
ThisWorkbook.Worksheets("Calc").Range("T2").Value = ThisWorkbook.Worksheets("Prep").Range("B16").Value
ThisWorkbook.Worksheets("Calc").Range("V2").Value = ThisWorkbook.Worksheets("Prep").Range("B17").Value
ThisWorkbook.Worksheets("Calc").Range("X2").Value = ThisWorkbook.Worksheets("Prep").Range("B18").Value

z = ThisWorkbook.Worksheets("Prep").Range("G4").Value
' Add the required number of rows to the Results table in sheet Calc
Do Until z = 0
Set tblRow = tbl.ListRows.Add
tblRow.Range.Offset(-1).Copy
tblRow.Range.PasteSpecial xlPasteFormulasAndNumberFormats
z = z - 1
Loop

' Hides the columns that are not required
    Dim C1 As Range
    For Each C1 In ThisWorkbook.Worksheets("Calc").Range("F1:Y1")
       C1.EntireColumn.Hidden = C1.Value = "0"
       Next C1
       
' Copies the formulas for SMALL and LARGE
' Range("D4:D8").FormulaR1C1 = "=IFERROR(AGGREGATE(15,6,RC6:RC24/(RC6:RC24<>"""")/(R7C7:R7C25=""COMPLIANT""),1),""No Data"")"
' Range("E4:E8").FormulaR1C1 = "=IFERROR(AGGREGATE(14,6,RC6:RC24/(RC6:RC24<>"""")/(R7C7:R7C25=""COMPLIANT""),1),""No Data"")"

' Protects the sheet
    If ChangeProtection = True Then .Protect (MyPassword)
End With
Application.CutCopyMode = False
Worksheets("Calc").Activate
Range("F4").Select
End Sub

 

[jasonb75]

Going back to the sample in post 10, exactly what range does the 'Results' table cover before you run the code?
If it's not full of confidential data, could you perhaps upload a copy of the workbook to dropbox or similar, where there is data ready to be processed, so that the next thing to do is run the code.

 

[JARichard74]

$A$3:$Y$4 with ROW 3 being the header row

 

[jasonb75]

Did you catch my edit above?
There is something (probably simple) that I'm not seeing, it would help to have the actual file to run a test on if that is possible.
Ideally with a clean 'Calc' sheet and data in the 'Prep' sheet that is ready to be copied over.

 

[JARichard74]

OK however I do not believe I can upload an Excel file to the BB

 

[jasonb75]

No, you would need to use service like dropbox and post the link here.

 

[JARichard74]

Selection_tool_test.xlsm

drive.google.com