Help with MAXIFS and MINIFS

JARichard74

Board Regular
Joined
Dec 16, 2019
Messages
100
Office Version
  1. 365
Platform
  1. Windows
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
VBA Code:
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

Well-known Member
Joined
Dec 30, 2008
Messages
12,438
Office Version
  1. 365
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

JARichard74

Board Regular
Joined
Dec 16, 2019
Messages
100
Office Version
  1. 365
Platform
  1. Windows
I get a compile error, Syntax error. Have not been able to figure out what the error is
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,438
Office Version
  1. 365
Platform
  1. Windows
Oops, that was my bad, I forgot to double up the double quotes in the formulas, it should have been
VBA Code:
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

Board Regular
Joined
Dec 16, 2019
Messages
100
Office Version
  1. 365
Platform
  1. Windows
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.
VBA Code:
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

Well-known Member
Joined
Dec 30, 2008
Messages
12,438
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,438
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Dec 16, 2019
Messages
100
Office Version
  1. 365
Platform
  1. Windows
OK however I do not believe I can upload an Excel file to the BB
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,438
Office Version
  1. 365
Platform
  1. Windows
No, you would need to use service like dropbox and post the link here.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,491
Messages
5,625,075
Members
416,068
Latest member
seba_s

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top